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 works in Informix SQL
SELECT * FROM mytable m_alias
WHERE sequence =
(SELECT MAX(sequence) FROM mytable
WHERE key_col = m_alias.key_col)
Barry