• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Getting the "Most Recent" Record via SQL

 
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:



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
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, I think I might have found a workable solution to this on my own. I simply used a nested query, like this:



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.
 
author & internet detective
Posts: 42163
937
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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"
 
Sheriff
Posts: 13411
Firefox Browser VI Editor Redhat
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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???
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.




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


then only you can modify your query as




thanks
[ January 21, 2005: Message edited by: Shailesh Chandra ]
 
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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



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
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic