informix sql

jeanvaljean

My sql has a temp table with 76 rows in it.
The fields are reference, status, and date.
There can be more than one row for each reference, but with differing dates.
What select syntax is needed to only return the row with the latest date,
dropping all others ?
I thought I knew SQL, but haven't used it for many years.
I would like to create another temp table, for further selects later on.
Many thanks

Butters

Here are some options, I don't have an informix DB to check syntax, but its pretty much stardard.

If you just want the date:

Select max(date) from tablename

If you want the latest date and highest reference#

Select TOP 1 max(date), reference from tablename 
group by reference 
order by max(date) desc, reference desc

If you want all rows with the latest date (there may be more than 1 row on if date is the same):

Select max(date), reference, status from tablename 
group by reference, status, date  
having date in (Select max(date) from tablename)

If you want 1 row with the latest date (there may be more than 1 row so a sort is applied):

Select TOP 1 max(date) date, reference, status from tablename 
group by reference, status, date  
having date in (Select max(date) from tablename)
order by date, reference

jeanvaljean

thanks butters : got it working OK !
sorry for the delay in getting back to you !

Butters wrote:

Here are some options, I don't have an informix DB to check syntax, but its pretty much stardard.

If you just want the date:

Select max(date) from tablename

If you want the latest date and highest reference#

Select TOP 1 max(date), reference from tablename 
group by reference 
order by max(date) desc, reference desc

If you want all rows with the latest date (there may be more than 1 row on if date is the same):

Select max(date), reference, status from tablename 
group by reference, status, date  
having date in (Select max(date) from tablename)

If you want 1 row with the latest date (there may be more than 1 row so a sort is applied):

Select TOP 1 max(date) date, reference, status from tablename 
group by reference, status, date  
having date in (Select max(date) from tablename)
order by date, reference