Re: Newbie : Find newest date
Date: 1995/11/10
Message-ID: <Pine.SUN.3.91.951110104858.8403E-100000_at_seatimes>#1/1
On Thu, 9 Nov 1995, Jesper Clausen wrote:
> Article Price Date
> ---------------------------------------
> a 2.00 11-NOV-93
> a 2.50 11-NOV-95
> b 1.00 11-DEC-94
> b 2.40 06-JUN-95
>
> How do i create a select statement which retrieve the row with the highest
> date ? - what i wan't is :
>
> a 2.50 11-NOV-95
> b 2.40 06-JUN-95
>
select article, max(price), max(date) from mytable x where date=(select max(date) from mytable y
where x.article = y.article)
group by article;
Please note: the max(price), max(date) on the first line is needed only to satisfy the group by syntax. In fact, the inner select will pick the row with the max(date) for each article. Assuming that article + date forms an unigue key, then there will be only one price for select to locate the max of. However, if there are multiple entries for the article and date, then the above will return the single record having the highest price on the most recent date. -- You could change the inner select to get the max of price first if you were interested in the most recent date on which the highest price was entered. But that wasn't the way you put the question so we took the orthoganal view.
+----------------------------------------------------+ | Steve Butler Voice: 206-464-2998 | | The Seattle Times Fax: 206-382-8898 | | PO Box 70 Internet: sbut-is_at_seatimes.com | | Seattle, WA 98111 Packet: KG7JE_at_N6EQZ.WA | +----------------------------------------------------+All standard and non-standard disclaimers apply. All other sources are annonymous. Received on Fri Nov 10 1995 - 00:00:00 CET