Retreive the most recent row

[MikeMcKeown]

Hi,

I've got a table that holds product information, this table is also used to archive the information on the products so that more than one row can exist for each product code. I need to retrieve the 'live' information, there is also a date field and I need to retrieve the row with the most recent date. Can anyone explain how to do this in SQL please? Are there any built in functions that make the job easy?

Thanks in advance,

Mike McKeown

 

[blom0344]

Generally, use a subselect:

Select * from table where datefield =
(Select max(datefield) from table)

Or use fetch clause to get top 1 row:

Select * from table
Order by datefield desc
Fetch first 1 row only;

This last one will work only with timestamps or with dates being unique in the table

T. Blom
Information analyst
tbl@shimano-eu.com