Re: Daily SQL Quiz
On May 5, 5:40am, Scott Ford wrote:
> Subject: Daily SQL Quiz
> 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.
>
This is where we have to change direction.
If you leave the max where it is then you get into all sorts of problems with
multiple nested selects etc.
This works for me by moving the selection of max(instance_date) into a
correlated subquery :-
SELECT a.task_id,
a.instance_date,
a.other_field
FROM testtable a
WHERE
a.instance_date = (
SELECT MAX(instance_date) FROM testtable
WHERE task_id = a.task_id
)
Correlated SubQueries are notoriously inefficent as they execute the sub-query
once for every row
in the table. :-(
However (According to recent threads ) providing your composite key is defined
in the order stated
then it can at least use the index :-)
> 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!
>-- End of excerpt from Scott Ford
--
-----------------------------------------------------------------------------
Steve Weet - European Mis - Motorola Cellular Subscriber Group
Beechgreen Court, Chineham, Basingstoke, HANTS England.
Phone : +44 (0)256 790154 E-Mail stevew@chineham.euro.csg.mot.com
Fax : +44 (0)256 817481 Mobile : +44 (0)850 335105 Post : w10075