SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Victor: Jun 18, 2004

Stephen posted:

I've got two MySQL tables:
canines(id, name, etc.)
votes (id, canine_id)

I want to select the dogs, ordered by the number of votes it has.

My query:
code:
SELECT c.*, v.votecount  
FROM canines c 
LEFT JOIN (
 SELECT canine_id, COUNT(*) AS votecount 
 FROM votes 
 GROUP BY canine_id
) v ON v.canine_id = c.id 
ORDER BY v.votecount DESC
This works perfectly, but it runs extremely slowly. Is there a faster way of doing this query?

Edit: I should mention that I have an index on 'canine_id' on the votes table.

var1ety posted:

Aggregating the votes table is probably what's taking so long. The query will out of necessity need to do a full table scan to satisfy the request, which can take a long time when the number of rows is large. You can run the query on its own to verify.

Unless MySQL is even more retarded than I thought, this is wrong. Only an index scan of the index on canine_id should be required to satisfy the derived table query. Stephen, how long does the derived table query take to run? Perhaps replacing the * with canine_id in the COUNT expression would help...

# ? Aug 4, 2008 15:09

Adbot: ADBOT LOVES YOU

# ? May 14, 2024 20:49

Safety Shaun: Oct 20, 2004; the INTERNET!!!1

How do I make this query:

SELECT `ATT_IP`,
`ATT_TIMESTAMP`,
COUNT(* )
FROM ATTEMPTS
GROUP BY `ATT_IP`
ORDER BY `ATTEMPTS`.`ATT_TIMESTAMP` DESC

Spit out the most recent timestamp associated with each group of ATT_IP?

# ? Aug 4, 2008 18:48

chocojosh: Jun 9, 2007; D00D.

Safety Shaun posted:

How do I make this query:

SELECT `ATT_IP`,
`ATT_TIMESTAMP`,
COUNT(* )
FROM ATTEMPTS
GROUP BY `ATT_IP`
ORDER BY `ATTEMPTS`.`ATT_TIMESTAMP` DESC

Spit out the most recent timestamp associated with each group of ATT_IP?

Shouldn't that give you a syntax error since you're not grouping by ATT_TIMESTAMP or using ATT_TIMESTAMP in an aggregate operation? I think you want:

code:

SELECT   `ATT_IP`,
         [b]MAX(`ATT_TIMESTAMP`)[/b],
         COUNT(* )
FROM     ATTEMPTS
GROUP BY `ATT_IP`
ORDER BY `ATTEMPTS`.`ATT_TIMESTAMP` DESC

Here's some sample code I whipped up quickly

DECLARE @Temp TABLE (
A_IP nvarchar(100),
A_TimeStamp int
)

insert into @Temp(A_IP, A_TimeStamp) VALUES ('1', 1)
insert into @Temp(A_IP, A_TimeStamp) VALUES ('1', 2)
insert into @Temp(A_IP, A_TimeStamp) VALUES ('1', 3)
insert into @Temp(A_IP, A_TimeStamp) VALUES ('2', 10)
insert into @Temp(A_IP, A_TimeStamp) VALUES ('3', 5)
insert into @Temp(A_IP, A_TimeStamp) VALUES ('4', -1)
insert into @Temp(A_IP, A_TimeStamp) VALUES ('2', 12)

select A_IP, MAX(A_TimeStamp) As 'Most recent', count(*) As 'count'
from @Temp
group by A_IP
order by A_IP

# ? Aug 4, 2008 19:06

Safety Shaun: Oct 20, 2004; the INTERNET!!!1

chocojosh posted:

Here's some sample code I whipped up quickly

Thanks chocojosh, based on your code I managed to turn it into

SELECT `att_ip` , max( att_timestamp ) AS 'att_mostrecent', COUNT( * ) AS 'att_howmany'
FROM attempts
GROUP BY `att_ip`
ORDER BY `att_timestamp` DESC

Which does exactly what I want it to do.

I even learned a couple of new things. Mucho appreciated!

# ? Aug 4, 2008 19:13

Scarboy: Jan 31, 2001; Good Luck!

Let's say I've got two tables (not normalized completely for simplicity):

tickets
-id

ticket_errors (maximum 10 rows per ticket_id)
-ticket_id
-error (string, length 4)

Let's say a ticket has 5 entries in the ticket_errors table, is there a way to join these two tables in SQL and return one row with all the error strings appended in one field? An example would be "001 003 005 006 007".

The way I would do this is to get all the rows in whatever I'm using, looping through the rows while adding the error string to a string. However, I'm sure there is a better way to do it that I just can't think of. Any ideas?

Edit: Since I need the data in both forms (join table, and one string), and I have the error string available before inserting the data, I'm thinking of just keeping an errors field in the tickets table. Is this sort thing acceptable even though it would mean it isn't normalized? Or would that just be dependent on space constrictions, etc...

Scarboy fucked around with this message at 19:12 on Aug 5, 2008

# ? Aug 5, 2008 19:09

Aredna: Mar 17, 2007; Nap Ghost

What flavor of SQL?

There are a few ways to accomplish what you want.

# ? Aug 5, 2008 19:52

Scarboy: Jan 31, 2001; Good Luck!

Aredna posted:

What flavor of SQL?

There are a few ways to accomplish what you want.

Oracle, I'm not sure about the server version though.

# ? Aug 5, 2008 19:56

var1ety: Jul 26, 2004

Scarboy posted:

Oracle, I'm not sure about the server version though.

There's a pretty comprehensive thread on the topic on Ask Tom at the following URL:

http://asktom.oracle.com/pls/asktom/f?p=100:11:2397328357508087::::P11_QUESTION_ID:229614022562

# ? Aug 5, 2008 20:31

Scarboy: Jan 31, 2001; Good Luck!

var1ety posted:

There's a pretty comprehensive thread on the topic on Ask Tom at the following URL:

http://asktom.oracle.com/pls/asktom/f?p=100:11:2397328357508087::::P11_QUESTION_ID:229614022562

Nice article, the DECODE trick has been useful to me in the past but it's not what I want this time. The other methods are perfect for what I want to do, but since this data isn't changing (ever!) I think will just go with the errors column and waste some extra space.

# ? Aug 5, 2008 20:37

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

You can also use the oracle COLLECT statement

http://www.oracle-developer.net/display.php?id=306

# ? Aug 5, 2008 20:39

RagingHematoma: Apr 19, 2004; Goiters can be beautiful too!

Scarboy posted:

Let's say I've got two tables (not normalized completely for simplicity):

tickets
-id

ticket_errors (maximum 10 rows per ticket_id)
-ticket_id
-error (string, length 4)

Let's say a ticket has 5 entries in the ticket_errors table, is there a way to join these two tables in SQL and return one row with all the error strings appended in one field? An example would be "001 003 005 006 007".

The way I would do this is to get all the rows in whatever I'm using, looping through the rows while adding the error string to a string. However, I'm sure there is a better way to do it that I just can't think of. Any ideas?

Edit: Since I need the data in both forms (join table, and one string), and I have the error string available before inserting the data, I'm thinking of just keeping an errors field in the tickets table. Is this sort thing acceptable even though it would mean it isn't normalized? Or would that just be dependent on space constrictions, etc...

I threw this together, and it is probably a terrible way to go about this. I am not sure if Oracle has ROW_NUMBER() available. The solution technically works for the situation he described, but the performance will probably be terrible with a lot of rows. Also it is not dynamic, you would always have to know what your maximum number of possibilities would be.

Does anyone have a better solution that does not use a cursor? The COLLECT operator looks cool. Is there an MS SQL equivalent?

code:

CREATE TABLE #Ticket_errors
(ticket_id int, 
 error varchar(100) ) 

INSERT INTO #Ticket_errors
SELECT 6, 'ERROR1'  
INSERT INTO #Ticket_errors
SELECT 6, 'ERROR2'  
INSERT INTO #Ticket_errors
SELECT 6, 'ERROR3'  
INSERT INTO #Ticket_errors
SELECT 3, 'ERROR4'  
INSERT INTO #Ticket_errors
SELECT 3, 'ERROR5'  
INSERT INTO #Ticket_errors
SELECT 4, 'ERROR6'  
INSERT INTO #Ticket_errors
SELECT 5, 'ERROR7'  
INSERT INTO #Ticket_errors
SELECT 5, 'ERROR8'  
INSERT INTO #Ticket_errors
SELECT 5, 'ERROR9'  
INSERT INTO #Ticket_errors
SELECT 5, 'ERROR10'  

SELECT  Ticket_ID, 
		max(CASE when RowNum = 1 then Error + ',' ELSE '' END) +
		max(CASE when RowNum = 2 then Error + ',' ELSE '' END) +
		max(CASE when RowNum = 3 then Error + ',' ELSE '' END) +
		max(CASE when RowNum = 4 then Error + ',' ELSE '' END) +
		max(CASE when RowNum = 5 then Error + ',' ELSE '' END) +
		max(CASE when RowNum = 6 then Error + ',' ELSE '' END) +
		max(CASE when RowNum = 7 then Error + ',' ELSE '' END) +
		max(CASE when RowNum = 8 then Error + ',' ELSE '' END) +
		max(CASE when RowNum = 9 then Error + ',' ELSE '' END) +
	    max(CASE when RowNum = 10 then Error + ',' ELSE '' END)

from (	select Ticket_ID, 
	ROW_NUMBER() OVER (partition by ticket_ID order by ticket_ID ) as RowNum, 
	error
		FROM #Ticket_errors 
	) A
group by Ticket_ID


Tic_ID	(No column name)
3	ERROR4,ERROR5,
4	ERROR6,
5	ERROR7,ERROR8,ERROR9,ERROR10,
6	ERROR1,ERROR2,ERROR3,

RagingHematoma fucked around with this message at 00:51 on Aug 6, 2008

# ? Aug 5, 2008 20:55

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Need some normalization advice: I want to save a setting if people want an email sent daily, weekly, or monthly. Would the proper style be to just keep "1", "2", and "3" in the database, and link them to another table which defines 1 as Daily, 2 as Weekly, etc.? Or would the proper style be to simply store "Daily" in the table and don't use a lookup table?

Or, should I use numbers and not even bother with a lookup table, since the possibilities will never change, and it could just be handled in code?

# ? Aug 5, 2008 22:05

Stephen: Feb 6, 2004; Stoned

Golbez posted:

Need some normalization advice: I want to save a setting if people want an email sent daily, weekly, or monthly. Would the proper style be to just keep "1", "2", and "3" in the database, and link them to another table which defines 1 as Daily, 2 as Weekly, etc.? Or would the proper style be to simply store "Daily" in the table and don't use a lookup table?

Or, should I use numbers and not even bother with a lookup table, since the possibilities will never change, and it could just be handled in code?

The general rule I stole from Coding Horrors is "Normalize until it hurts". That is to say, normalize it for now, just to make the code easy to maintain, but in the future, if your list of recipients goes huge, you may want to just reference the ID's directly to improve query performance.

# ? Aug 5, 2008 23:02

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Stephen posted:

The general rule I stole from Coding Horrors is "Normalize until it hurts". That is to say, normalize it for now, just to make the code easy to maintain, but in the future, if your list of recipients goes huge, you may want to just reference the ID's directly to improve query performance.

I'm somewhat of a noob to normalization, so which would be the best option here?

The options:

code:

A: Use numbers, and tell the database what they mean
tblemails
  Frequency: 1, 2, or 3
tblfrequencies
  ID: 1, 2, or 3
  Name: Daily, Weekly, or Monthly

code:

B: Don't use numbers.
tblemails
  Frequency: Daily, Weekly, or Monthly

code:

C: Use numbers, but don't tell the database what they mean; handle the meaning in code, since the three possibilities will never change.
tblemails
  Frequency: 1, 2, or 3
PHP: "if 1, frequency = 'Daily'"

Which is the best practice? Right now, for simplicity, I'm going with #3...

Golbez fucked around with this message at 00:44 on Aug 6, 2008

# ? Aug 6, 2008 00:30

epswing: Nov 4, 2003; Soiled Meat

Depending on your RDBMS, you could use an enum field.

# ? Aug 6, 2008 07:16

Random Child: Jan 3, 2006; You don't know me.

Real quick question, I'm using MS reporting services to make a front-end to my database. I need to filter results based on Windows Authentication, specifically the user's network alias. I'm told this information is a parameter that is already in Reporting services, but I have no idea where it is or what it is called.

Can anyone help me out?

# ? Aug 6, 2008 14:53

var1ety: Jul 26, 2004

Golbez posted:

I'm somewhat of a noob to normalization, so which would be the best option here?

The options:

code:

A: Use numbers, and tell the database what they mean
tblemails
  Frequency: 1, 2, or 3
tblfrequencies
  ID: 1, 2, or 3
  Name: Daily, Weekly, or Monthly

code:

B: Don't use numbers.
tblemails
  Frequency: Daily, Weekly, or Monthly

code:

C: Use numbers, but don't tell the database what they mean; handle the meaning in code, since the three possibilities will never change.
tblemails
  Frequency: 1, 2, or 3
PHP: "if 1, frequency = 'Daily'"

Which is the best practice? Right now, for simplicity, I'm going with #3...

Nobody should ever use C. Ever.

A or B are fine. I would personally use A. Make sure you size your number column appropriately or you will reduce the benefit.

# ? Aug 6, 2008 15:11

6174: Dec 4, 2004

I've got a MS Access Database (not my choice, but it is only ~15k records, rarely modified, and never used by more than one user at once) I'm trying to query.

If I use

code:

SELECT *
FROM mloa_data INNER JOIN classification ON mloa_data.Classification_ID = classification.ID
WHERE ([classification]![Classification] Is Not Null);

I get the results I'm expecting. If I negate the statement to

code:

SELECT *
FROM mloa_data INNER JOIN classification ON mloa_data.Classification_ID = classification.ID
WHERE NOT ([classification]![Classification] Is Not Null);

code:

SELECT *
FROM mloa_data INNER JOIN classification ON mloa_data.Classification_ID = classification.ID
WHERE ([classification]![Classification] Is Null);

I get no results when there should be a couple thousand results.

I know very little about SQL. Is using "NOT" more complicated for some reason? Or is this just one of many reasons why Access is a bad choice?

edit: To clarify a little further. There are 15417 rows in the table mloa_data. The first query returns 12898 rows. I expect the remaining 2519 rows to be returned by both the 2nd and 3rd query. As it stands the 2nd and 3rd queries return 0 rows.

edit2: Changing the first query to <> '' instead of Is Not Null gives the same results. Negating this also still gives 0 results.

6174 fucked around with this message at 19:02 on Aug 6, 2008

# ? Aug 6, 2008 17:15

RagingHematoma: Apr 19, 2004; Goiters can be beautiful too!

var1ety posted:

Nobody should ever use C. Ever.

A or B are fine. I would personally use A. Make sure you size your number column appropriately or you will reduce the benefit.

Var1ety is right, never use option C. The first time I ever went on the assumption that "these things will never change" is when things start to become a disaster.

Also, keeping things away from the code and on the database level, make application maintenance much easier. I always find it easier to change an entry in a table rather than deploy a new set of code.

# ? Aug 6, 2008 17:17

Aredna: Mar 17, 2007; Nap Ghost

6174 posted:

...
I get the results I'm expecting. If I negate the statement to
code:
SELECT *
FROM mloa_data INNER JOIN classification ON mloa_data.Classification_ID = classification.ID
WHERE ([classification]![Classification] Is Not Null);
...

The problem is your inner join. You need a left join if you want to use IS NULL or IS NOT NULL correctly.

# ? Aug 6, 2008 19:14

6174: Dec 4, 2004

Aredna posted:

The problem is your inner join. You need a left join if you want to use IS NULL or IS NOT NULL correctly.

Thanks. I just tested it and it works.

# ? Aug 6, 2008 20:13

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Random Child posted:

Real quick question, I'm using MS reporting services to make a front-end to my database. I need to filter results based on Windows Authentication, specifically the user's network alias. I'm told this information is a parameter that is already in Reporting services, but I have no idea where it is or what it is called.

Can anyone help me out?

User!UserID

# ? Aug 6, 2008 23:32

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

var1ety posted:

Nobody should ever use C. Ever.

A or B are fine. I would personally use A. Make sure you size your number column appropriately or you will reduce the benefit.

How do you mean? I figure a Tinyint would be perfectly fine.

Thanks for the advice about skipping C. I was just hoping to avoid making another table (and I see no point in storing "Daily", etc. in the main table, when a single byte can handle it), but then again, I'm sure a basic lookup table of frequencies will be useful in more than one application.

# ? Aug 7, 2008 00:05

var1ety: Jul 26, 2004

Golbez posted:

How do you mean? I figure a Tinyint would be perfectly fine.

Thanks for the advice about skipping C. I was just hoping to avoid making another table (and I see no point in storing "Daily", etc. in the main table, when a single byte can handle it), but then again, I'm sure a basic lookup table of frequencies will be useful in more than one application.

It wasn't a deep insight, I just meant to choose a datatype appropriate for the expected cardinality of the numeric column. Oracle only has the NUMBER data type, and if you do not size it then it defaults to 22 bytes.

# ? Aug 7, 2008 01:06

Zoracle Zed: Jul 10, 2001

Golbez posted:

I'm somewhat of a noob to normalization, so which would be the best option here?

The options:
code:
C: Use numbers, but don't tell the database what they mean; handle the meaning in code, since the three possibilities will never change.
tblemails
  Frequency: 1, 2, or 3
PHP: "if 1, frequency = 'Daily'"
Which is the best practice? Right now, for simplicity, I'm going with #3...

You could also do:

code:

tblemails
  Period: 1, 7, or 30

Which would give the simplicity of #3 but the flexibility (or more) of the first two options.

# ? Aug 7, 2008 04:46

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

In MySQL, when you delete a row from a table it creates 'overhead', which from my understanding is the row still exists, it's just marked as deleted. To clear the overhead, I can do an OPTIMIZE TABLE. Is this supposed to just be done on a weekly basis with a cron job or what?

# ? Aug 7, 2008 10:01

MoNsTeR: Jun 29, 2002

Golbez posted:

I'm somewhat of a noob to normalization, so which would be the best option here?
...snip...
Which is the best practice? Right now, for simplicity, I'm going with #3...

You should absolutely always use A, complete with referential integrity.

Note that this does not mean you have to join in the reference table all the time. It's perfectly fine to write queries like "select * from entity where attribute = 1" when top performance is a concern. The key is that the reference table gives you 1) a place to look up the meanings of the codes and 2) enforcement on using only valid codes.

It may not seem like there's anything wrong with using a text field but 6 months later when you don't remember what the valid values are and didn't write them down anywhere, and don't remember what some of them mean, it'll become clear.

# ? Aug 7, 2008 17:53

Stephen: Feb 6, 2004; Stoned

MoNsTeR posted:

You should absolutely always use A, complete with referential integrity.

Note that this does not mean you have to join in the reference table all the time. It's perfectly fine to write queries like "select * from entity where attribute = 1" when top performance is a concern. The key is that the reference table gives you 1) a place to look up the meanings of the codes and 2) enforcement on using only valid codes.

It may not seem like there's anything wrong with using a text field but 6 months later when you don't remember what the valid values are and didn't write them down anywhere, and don't remember what some of them mean, it'll become clear.

This is exactly what I was getting at. Normalize like hell, and if once upon a time you need the improved performance, it's very easy to just use the reference id instead of the value name. I wish I'd given myself this advice when I first started programming oh god.

# ? Aug 7, 2008 19:54

Cheesemaster200: Feb 11, 2004; Guard of the Citadel

code:

SELECT i.*, 
		SUM(IF(rl.member_id = 0, 1, 0)) as disenchant_count, 
                COUNT(DISTINCT(req.member_id)) as request_count,
                COUNT(r.raid_id) as drop_count, 
		MIN(r.raid_date) as first_drop, 
		MAX(r.raid_date) as last_drop  
		FROM rpp_items i 
		LEFT JOIN rpp_raid_loot rl ON rl.item_id = i.item_id  
		LEFT JOIN rpp_raids r ON r.raid_id = rl.raid_id 
		LEFT JOIN rpp_requests req ON req.item_id = i.item_id 
		GROUP BY i.item_id
		ORDER BY drop_count DESC LIMIT 5

Here is mySQL query I am screwing with for a little project of mine for my former WoW guild page which I kind of still run for them.

Here is a rundown of what is going on:

rpp_raids is the raid table
rpp_raid_loot is the raid drop table (all items which dropped)
rpp_items is the total item table (no drop information)
rpp_requests is the table for all requests.

I would like this query to give me a count of total requests and total drops.

The drops will work perfectly until I put in the JOIN for the requests, at which point it will then multiply the drops by the requests.

I am going nuts trying to figure out why it is doing this, any help would be greatly appreciated

# ? Aug 8, 2008 16:41

Stephen: Feb 6, 2004; Stoned

Cheesemaster200 posted:

...

code:

SELECT 
	i.*, 
	rl.drop_count, 
	req.request_count  
FROM rpp_items i 
LEFT JOIN (
	SELECT item_id, COUNT(*) AS drop_count 
	FROM rpp_raid_loot 
	GROUP BY item_id 
) rl ON rl.item_id = i.item_id 
LEFT JOIN (
	SELECT item_id, COUNT(*) AS request_count 
	FROM rpp_requests 
	GROUP BY item_id 
) req ON req.item_id = i.item_id 
ORDER BY drop_count DESC

This should give you a count of all dropped items, and the number of requests for each item.

Just a heads up though, this is a pretty slow query in MySQL especially once you start getting a lot of requests and drops.

# ? Aug 8, 2008 17:06

Cheesemaster200: Feb 11, 2004; Guard of the Citadel

edit
NM, I got it, stupid mistake

Thanks for the help, by the way

Edit Edit:

Actually, is there a way so that if request_count comes back as nothing, it displays "0" as default, instead of "null".

Thanks!

Cheesemaster200 fucked around with this message at 17:42 on Aug 8, 2008

# ? Aug 8, 2008 17:23

stack: Nov 28, 2000

Cheesemaster200 posted:

Actually, is there a way so that if request_count comes back as nothing, it displays "0" as default, instead of "null".

Thanks!

IFNULL({expression}, 0)

So for example SUM(columnA) becomes SUM(IFNULL(columnA, 0)) or IFNULL(SUM(columnA), 0) depending on what you're going for.

stack fucked around with this message at 18:37 on Aug 8, 2008

# ? Aug 8, 2008 18:33

toby: Dec 4, 2002

stack posted:

IFNULL({expression}, 0)

So for example SUM(columnA) becomes SUM(IFNULL(columnA, 0)) or IFNULL(SUM(columnA), 0) depending on what you're going for.

Could also use

COALESCE(columnA,0)

Right?

toby fucked around with this message at 20:49 on Aug 8, 2008

# ? Aug 8, 2008 20:44

Carabus: Sep 30, 2002; Booya.

I would appreciate some advise on function/algorithm design, no code yet. I'm interested in data mining a table recording web page views. There are nearly 1 million rows (don't ask) but I am only interested in the 80k or so which have nonempty user columns. Another relevant column is page_id.

In order to do sane analysis of this data I need to count records for each user, for each page; then I can record in another table the user, page, and the total number of views.

However, with several thousand pages and users doing this would seem to require several million of queries with MySQL and other horrors. Is there a more sane way?

# ? Aug 8, 2008 21:12

stack: Nov 28, 2000

toby posted:

Could also use

COALESCE(columnA,0)

Right?

You could but some might frown upon it. I probably would change it to IFNULL(). (edit: Only if I was in a cranky mood though.)

COALESCE is best used when you have many values that might be null and you want the first one that isn't null for example (rough impromptu example):

code:

SELECT 
  s.name AS state, c.name AS county, m.name AS muni, 
  COALESCE(m.tax_rate, c.tax_rate, s.tax_rate) AS tax_rate
FROM states s
  -- LEFT JOIN so I get states without counties, and counties without munis
  LEFT JOIN counties AS c USING (state_id)
  LEFT JOIN municipalities AS m USING (county_id)
WHERE
  s.region = 3

With the above and using COALESCE I don't have to have a tax rate value defined for each county or municipality if they don't differ from the default state tax rate.

(edit edit: no offense meant if you already understood COALESCE()

)

stack fucked around with this message at 21:40 on Aug 8, 2008

# ? Aug 8, 2008 21:32

var1ety: Jul 26, 2004

Carabus posted:

I would appreciate some advise on function/algorithm design, no code yet. I'm interested in data mining a table recording web page views. There are nearly 1 million rows (don't ask) but I am only interested in the 80k or so which have nonempty user columns. Another relevant column is page_id.

In order to do sane analysis of this data I need to count records for each user, for each page; then I can record in another table the user, page, and the total number of views.

However, with several thousand pages and users doing this would seem to require several million of queries with MySQL and other horrors. Is there a more sane way?

Isn't this

code:

select user, page_id, count(*) AS ct
  from webstats
 where user is not null
 group by user, page_id

Depending on the proportion of null/non-null user values it might make sense to add an index on the column.

# ? Aug 8, 2008 21:33

Aredna: Mar 17, 2007; Nap Ghost

stack posted:

You could but some might frown upon it. I probably would change it to IFNULL(). (edit: Only if I was in a cranky mood though.)

COALESCE is best used when you have many values that might be null and you want the first one that isn't null for example (rough impromptu example):

Why is IFNULL() superior to COALESCE?

# ? Aug 8, 2008 21:36

stack: Nov 28, 2000

Aredna posted:

Why is IFNULL() superior to COALESCE?

I didn't mean or intend to imply it was. COALESCE(columnA, 0) will do the same exact thing, return 0 if columnA is NULL or columnA if it is not.

# ? Aug 8, 2008 21:51

var1ety: Jul 26, 2004

Aredna posted:

Why is IFNULL() superior to COALESCE?

In a mixed-version environment where one version does not support COALESCE it might make sense to standardize on operators available on all Production versions of the database. We are forced to, in some situations, ignore new features in our databases so that scripts will run with minimal modification on older versions.

My personal preference is to use COALESCE/CASE in all situations and ignore our database's large number of null-handling functions.

# ? Aug 8, 2008 21:53

Adbot: ADBOT LOVES YOU

# ? May 14, 2024 20:49

LordLobo: Dec 12, 2003; Not
gonna
take it
anymore

This is specifically a SQL Server 2005 question:

Is it possible to query the SYS objects to get the username of someone who created a stored procedure?

Someone wrote something so stupid I need to beat them, severely.

# ? Aug 8, 2008 22:04

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »