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?
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.
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.