Re: Daily SQL quiz - revisited
In article <1993May13.170650.27426@wvus.org>, sford@wvus.org (Scott Ford) writes:
|> 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.
select * from foo select * from foo
group by key_col
having sequence = max(sequence) key_col sequence comment
------- -------- --------------
key_col sequence comment 7 1 key: 7, seq: 1
------- -------- -------------- 7 2 key: 7, seq: 2
7 3 key: 7, seq: 3 7 3 key: 7, seq: 3
8 2 key: 8, seq: 2 8 1 key: 8, seq: 1
9 4 key: 9, seq: 4 8 2 key: 8, seq: 2
9 1 key: 9, seq: 1
(3 rows affected) 9 2 key: 9, seq: 2
9 3 key: 9, seq: 3
9 4 key: 9, seq: 4
(9 rows affected)
--
Robert Garvey robert@sybase.com {sun,lll-tis,pyramid,pacbell}!sybase!robert
Sybase, Inc 6475 Christie Ave Emeryville, CA 94608-1010
Opinions may be attributed to poster, not necessarily related to Sybase.