Re: Daily SQL quiz - revisited
In article <1993May13.170650.27426@wvus.org> sford@wvus.org (Scott Ford) writes:
>Thanks to all who contributed to the SQL quiz -- trying to get a number
>of rows back when a max(col) is involved. The responses ranged from, "you
>probably can't do this", to "this is basic". Given the sum of the
>responses, I would have to say that the former is likely more accurate.
>
>Perhaps I can simplify the question (albeit not the resolution, however):
>
>Say we have a table that contains many rows. For each row, we have a
>column called "sequence", such that we could have the following:
>
> key_col sequence many other columns
> ------- -------- ------------------
> 16 1
> 16 2
> 17 1
> 17 2
> 17 3...
>
>Okay, you get the picture. If we know we want the most current record
>for key value '16', we merely state, "select * from mytable where key_col
>= 16 and sequence = (select max sequence from mytable where key_col =
>16)".
>
>However, what do we do if we want the current records for each of the
>different key values. This is the basic challenge of the SQL quiz.
>We're beginning to think it cannot be done easily and that we should just
>throw in the towel by adding one more column to the table called
>"current". Then only the most current record for each key value would have
>the value "y" for the column current and one could say, "select * from
>mytable where current = "y"". Of course, we must then be careful to keep
>the "current" column correct and to never have more than one "y" value
>for any key value at any given moment.
>
>Thanks much for your responses to this nagging issue...
I suppose purists would want "single query" solutions to this. Myself,
an impurist, would happily write:
select key_col, max(sequence) max_seq
from table_1
group by 1
into temp got_em ;
select table_1.*
from table_1, got_em
where table_1.key_col = got_em.key_col
and table_1.sequence = got_em.max_seq
- Paul