Re: Help with this SQL statement please...

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Mon, 16 May 2005 12:04:12 +0200
Message-ID: <42886fcf$0$16254$626a14ce@news.free.fr>

"harry" <spammemothers_at_yahoo.co.uk> a écrit dans le message de news:nIZhe.32707$G8.24337_at_text.news.blueyonder.co.uk...
| I have a table called milestone containing this data -
|
| mile_id actual_date
| ------- -----------
| 3 12/02/2003
| 1 10/10/2002
| 2 31/01/2003
| 4
| 5
| 6
|
| I want a SQL statement to return ONLY the row with the highest mile_id AND
| with an actual_date. This works -
|
| SELECT MAX(mi.mile_id), MAX(mi.actual_date) FROM milestone mi WHERE AND
| mi.actual_date IS NOT NULL
|
| returning
|
| 3 12/02/2003
|
|
| which is correct but is this the best way? - the bit I'm really querying is
| the "MAX(mi.actual_date)"
|
| many thanks
|
| harry
|
|

You got the correct result by chance.
Switch actual_date value from mile_id 2 and 3 and you have a wrong result.

Have a look at MAX analytic function:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions2a.htm#81409 http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions70a.htm#84290

Regards
Michel Cadot Received on Mon May 16 2005 - 05:04:12 CDT