Distinct query

[bugsy]

Hey all

I have data in the table.
(various IDs - in example below just 1)
Each ID has date and status.

I need to pull each ID only ones
for MAX date

If there are 2 like that (with different statuses) i need the one with lowest importance. Any advise ?


Status LogID Date ID Date Importance
ssss 02/04/2008 02/04/2008 ghj 6
ssss 02/04/2008 02/04/2008 ghj 6
tttt 02/04/2008 02/04/2008 ghj 5
tttt 02/04/2008 02/04/2008 ghj 5
rrrrrr 02/07/2008 02/07/2008 ghj 4
rrrrrr 02/07/2008 02/07/2008 ghj 4
ssss 02/08/2008 02/08/2008 ghj 6
ssss 02/08/2008 02/08/2008 ghj 6
ssss 02/11/2008 02/11/2008 ghj 6
ssss 02/11/2008 02/11/2008 ghj 6
tttt 02/11/2008 02/11/2008 ghj 5
tttt 02/11/2008 02/11/2008 ghj 5

 

[neileg]

Apply max to the date and also apply max or min to the status (not clear how the scoring goes).

 

[bugsy]

Apply max to the date and also apply max or min to the status (not clear how the scoring goes).

I did, but then a record with date of 02/11/2008 and Importance of 4 comes back
such record doesn't even exist.
The record i need to select has a date of 02/11/2008 and importance of 5.
Also, once i get record with importance 5 - i need IT'S Status whih is 'tttt', not MIN (Status) or Max9Status)

 

[neileg]

Oops.

The easiest way is to use nested queries. Do one query that applies max to the date and then base a second query on that and apply the test to the importance.