Re: Help with select statement

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Tue, 30 Jan 2001 17:01:31 +0100
Message-ID: <956ogr$5nl$1@s1.read.news.oleane.net>

"Dan Gibbons" <Daniel.Gibbons_at_SEB.co.uk> a écrit dans le message news: 1aBd6.33$u7.98_at_nntpserver.swip.net...
> Hi,
>
> I need some help with the following table please:
>
> CODE UPDATE_DATE CLOSE_PRICE
> 1749 2001-01-29 19:31:36 1.175
> 1749 2001-01-26 19:31:30 1.175
> 1749 2001-01-25 19:31:33 1.195
> 1749 2001-01-24 19:31:37 1.175
> 1748 2001-01-23 19:31:42 1.11
> 1748 2001-01-22 19:31:37 1.04
> 1748 2001-01-19 19:20:58 1.035
>
>
> In Oracle is there any way to select one record for the latest UPDATE_DATE
> so I can get something like
>
> CODE UPDATE_DATE CLOSE_PRICE
> 1749 2001-01-29 19:31:36 1.175
> 1748 2001-01-23 19:31:42 1.11
>
> Basically for each CODE I need to get the latest CLOSE_PRICE.
>
> Thanks in advance
>
> Dan

select a.code, a.update_date, a.close_price from (select code, max(update_date) maxdate from <table>

      group by code) b,
     <table> a

where b.code = a.code
  and b.maxdate = a.update_date
/

--
Have a nice day
Michel

Received on Tue Jan 30 2001 - 10:01:31 CST