Re: Daily SQL Quiz
->From: sford@wvus.org (Scott Ford)
->Subject: Daily SQL Quiz
->Date: Wed, 05 May 93 05:40:53 GMT
->Reply-To: sford@wvus.org (Scott Ford)
->Organization: World Vision U.S., Monrovia, CA
->
->And now for today's SQL pop quiz:
->
->Suppose we have a table that contains a composite primary key on columns
->task_id and instance_date.
->
->Now suppose that the table is populated with lots of data and we want to
->get a listing of all tasks of a certain type, but only the latest date
->for each type. Easy, right?
->
->Sure. select max(instance_date) from table group by task_type. Gee,
->that was easy. All we had to do was group by the max() column (otherwise
->we'd get only one row back).
->
->Now suppose there's another column in the table that we want to select.
->If it has differing values, we have a problem. This is because we'll get
->a row back for every differnt value in that other column.
->
->If we are obstinate and refuse to use a programming language (other than
->SQL) or a temporary table, can we still get the result we want?
->
->Prizes (if any) to be named later. Appreciation included now - thanks
->for your help!
->
It depends on the results you want. Being obstinate does limit your options somewhat. :-(
You can get *representative* values of the 'other' column thus:
SELECT task_id, MAX(instance_date), MIN(other), MAX(other), AVG(other)
FROM task_table
WHERE task_id = 'certain type'
GROUP BY 1;
MAX and MIN work for most types (obviously not BLOBs, etc.). Of course,
AVG will only work for numeric type columns.
Hint: use DATE ( AVG(instance_date) ), because just AVG(instance_date)
displays as if it were a FLOAT.
If this is not quite what you wanted, could you restate the problem?
Regards,
Alan
+------------------------------+---------------------------------------+
| R. Alan Popiel | Internet: alan@den.mmc.com |
| Martin Marietta, LSC | ( Please note: My opinions do not ) |
| P.O. Box 179, M/S 5422 | ( represent official Martin policy. ) |
| Denver, Colorado 80201-0179 | Voice: 303-977-9998 |
+------------------------------+---------------------------------------+