Returm the most recent record

[Raul2005]

This is just a sample it could be thounsand records like this. What I am looking is to detect the most recent date if and only if the status was pending.
For example the result from the sample should return
1 New Issue 2005-11-23 10:54:24.113 Pending
3 third 2005-11-23 10:54:24.177 Pending
These 2 records contain the 2 criteries first has to be status equal Pending and has to be the most recent record.If the most recent record for an specific product is different than Pending this won't be part of our result.

ID Product Date Status
1 New Issue 2005-10-23 10:25:17.483 Pending
1 New Issue 2005-10-24 10:43:02.963 Accepted
1 New Issue 2005-11-10 10:53:03.237 Denied
1 New Issue 2005-11-23 10:54:24.113 Pending
2 Secondary 2005-11-14 10:25:17.340 Pending
2 Secondary 2005-11-15 10:43:03.043 Accepted
2 Secondary 2005-11-16 10:53:03.300 Denied
2 Secondary 2005-11-23 10:54:24.177 Pending
2 Secondary 2005-11-23 10:55:04.973 Accepted

3 third 2005-10-14 10:25:17.340 Pending
3 third 2005-10-15 10:43:03.043 Accepted
3 third 2005-11-23 10:54:24.177 Pending


I will appreciate any help.

Thanks

 

[Dashley]

raul2005

Im not sure how many records you ant returned. If you looking for just the top one this will work.

Select MAX(date) from wherever where status='Pending"

If your looking for mutliple reocrds with the most current date at top

Select product from wherever where status ='pending'
order by date DESC


DESC is descending order

 

[r937]

use a correlated subquery

select ID
     , Product
     , Date
     , Status
  from daTable as XX
 where Status = 'Pending'
   and Date =
       ( select max(Date)
           from daTable
          where ID = XX.ID )

r937.com | rudy.ca

 

[toon10]

This derived table solution may work as an alternative:

SELECT A.ID, A.Product, A.Date, A.Status
FROM daTable A INNER JOIN
(SELECT ID, MAX(Date)
AS Date
FROM daTable
WHERE Status = ‘Pending’
GROUP BY ID) B ON A.ID = B.ID AND
A.Date = B.Date
WHERE A.Status = ‘Pending’
order by date DESC

 

[r937]

toon, leaving aside the question of whether a derived table is faster than a correlated subquery (see

http://r937.com/multiple_relationships.html

for a link to an interesting article), your solution returns the latest pending row per id, whereas mine returns the latest row per id only if it's a pending row -- significant difference, eh?

r937.com | rudy.ca