Getting the "Most Recent" Record via SQL

[Corey McGlone]

Hi all,

I've run across a SQL statement that I need to write and, in order to get it working, I'm going to have to accomplish something I've never tried before. I'm hoping there's some simple SQL keyword that will help me out. Hopefully, someone here knows a simple solution.

Anyway, here's the basis of the problem - I'll try to keep the extraneous details out of this.

In my table, each record has a group identifier and a date associated with it. So, for example, if I were to pull all records with a given group identifier, I might get a handful of records (could be 0 - n). In my case, I want just the most recent record, but I don't necessarily know what date that might be.

So, if I were to execute this SQL statement:

SELECT * FROM MyTable WHERE GroupID = '10' ORDER BY Service_Date DESC

I'll get back a list of records in which I really want just the first record. Is there an easy way to get just that record?

Thanks,
Corey

[Corey McGlone]

Well, I think I might have found a workable solution to this on my own. I simply used a nested query, like this:

SELECT * FROM MyTable WHERE GroupID = '10' AND Service_Date =
( SELECT MAX(Service_Date) FROM MyTable WHERE GroupID = '10' )

Is that the best way to go about this? It seems to work, but I don't know about efficiency issues. Also, if two records have identical dates, I'll get two records back. I don't know that I'd really want to get them both back.

[Jeanne Boyarsky]

Corey,
Most database support a clause to get the first X rows. In Oracle, you can use the rownum. In db2, you can use "fetch first row only"

[Ben Souther]

If you had a lot of records with groupID equal to 10, I would think using the subquery would be a lot more efficient because it eliminates the need to perform the sort.

Right, no, maybe???

[Shailesh Chandra]

Corey,

If you are using oracle then there are few pretty methods for getting latest record like RANK(), DENSE_RANK() or ROW_NUMBER() which will solve your problem.

 
SELECT COL1,COL2, SERVICE_DATE  FROM 
(
SELECT COL1,COL2, SERVICE_DATE , 
DENSE_RANK() OVER (ORDER BY SERVICE_DATE DESC nulls last) RN 
FROM TABLE
)
WHERE RN = 1
 

even if you make WHERE RN = 2 you will get second latest record

now the best benefit which you can achieve is

If you want to get lastest SERVICE_DATE for all col1 in table.

like

 
 
           col1                    SERVICE_DATE 
 
           val 1                 latest update time of val 1
 
           val 2                 latest update time of val 2
 

then only you can modify your query as

  
 
SELECT COL1,COL2, SERVICE_DATE  FROM 
(
SELECT COL1,COL2, SERVICE_DATE , 
DENSE_RANK() OVER (PARTITION BY COL1 ORDER BY SERVICE_DATE DESC nulls last) RN 
FROM TABLE
)
WHERE RN = 1

thanks
[ January 21, 2005: Message edited by: Shailesh Chandra ]

[Michael Matola]

CM:
SELECT * FROM MyTable WHERE GroupID = '10' AND Service_Date =
( SELECT MAX(Service_Date) FROM MyTable WHERE GroupID = '10' )

Is that the best way to go about this?

The typical solution is

SELECT m.* 
  FROM MyTable m 
 WHERE m.GroupID = '10' 
   AND m.Service_Date =
( SELECT MAX(m2.Service_Date) FROM MyTable m2 WHERE m2.GroupID = m.GroupId )

I've posted a couple times on this sort of thing. Here's a discussion.

Also, if two records have identical dates, I'll get two records back. I don't know that I'd really want to get them both back.

Well, you'd have that exact same issue if you were qualifying on a specific date (where m.Service_Date = '20-JAN-2005', for example), so the issue of multiple rows returned for a given date is independent of the issue of finding the maximum date across an arbitrary grouping.

[Michael Matola]

Most database support a clause to get the first X rows. In Oracle, you can use the rownum.

Using rownum for this sort of thing is clumsy because you have to pull some aliasing and inline view tricks. Rownum is evil.
[ January 21, 2005: Message edited by: Michael Matola ]

[Michael Matola]

And, incidentally, when you say '"most recent,"' that may imply the current date as a cutoff, in which case the subquery would be

... SELECT MAX(m2.Service_Date) FROM MyTable m2 WHERE m2.GroupID = m.GroupId and m2.Service_Date <= sysdate ...

because max(m2.Service_Date) alone here would select a date in the future, if future dates occur in your data.

[Corey McGlone]

Thanks, everyone, for the tips. I still need to clarify some of the business rules behind this query so I don't have a final solution yet, but the tips I got here certainly help.

Thanks,
Corey