Re: Tricky SQL Problem

From: James Lorenzen <james_lorenzen_at_allianzlife.com>
Date: Fri, 30 Apr 1999 13:26:25 GMT
Message-ID: <7gcb1r$mc5$1@nnrp1.dejanews.com>

This call for the "subselect". There are several ways of defining the subselect that will all return the desired answer set.

SELECT * FROM table
WHERE (id,datetime) IN (SELECT id, max(datetime)

                          FROM table GROUP BY id) ;

HTH
James

In article <925471736.21781.0.nnrp-07.d4e48d0d_at_news.demon.co.uk>, "Matt Randle" <matt_at_imat.demon.co.uk> wrote:
> We have a table that is keyed on a unique ID + a Date Time value. Using
> Oracle SQL is there any way of finding the most recent assignments by ID.
>
> For example, if the table was populated as follows (British dates),
>
> ID Date Time + Additional Columns
>
> 1 1/1/99
> 1 5/3/99
> 1 6/4/99
> 2 4/4/99
> 2 28/5/99
> 3 1/4/99
> 4 27/2/99
> 4 5/5/99
> 4 9/8/99
>
> The result set should be,
>
> ID Date Time + Additional Columns
>
> 1 6/4/99
> 2 28/5/99
> 3 1/4/99
> 4 9/8/99
>
> Thanks in advance,
>
> Matt.
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Apr 30 1999 - 08:26:25 CDT