May 2, 2003 at 4:26 pm
I am retrieving the max date field from a table as in Select Max(StartDate) but I also want to get another column that is in the same row as that piece of data. Is there any way to do this without re-querying the table looking for a date equal to that particular date? The problem is that there could be multiple rows with that same date value but other columns would be different.
May 5, 2003 at 3:03 am
One alternative is to use TOP clause
SELECT TOP 1 date_column,<other columns>,...
FROM table
WHERE <condition>
ORDER BY date_column DESC
May 5, 2003 at 5:26 am
Another solution is using a join. This solution will give you more than one record if the max(StartDate) is not unique in the table.
SELECT t.<needed columns>
FROM Table t
INNER JOIN (SELECT MAX(StartDate) StartDate
FROM Table
WHERE <conditions>
) temp
ON t.StartDate = temp.StartDate
It is easy to extend this query to match all records grouped by some criterium, by adding a GROUP BY clause in the 'temp' query. A typical example would be to get the last purchase for each client in a purchasing system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply