Need help writting a query to return the two highest values

[Poobear1929]

I am trying to write a query that will return the two most current dates in a table titled Fitness. Here is what I have so far. It will return the most current date, but I can't figure out how to get the next date after that.

SELECT Fitness_Test.Member_ID, Max(Fitness_Test.Date) AS MaxOfDate
FROM Fitness_Test
GROUP BY Fitness_Test.Member_ID;


Thank you

 

[lespaul]

You may want to rename your field DATE to something like Test_Date; DATE is a key/reserved word in most databases. This query should work:

SELECT TOP 2 * FROM (SELECT MEMBER_ID, [DATE] FROM Fitness_Test ORDER BY [DATE] DESC)

Leslie

 

[PHV]

SELECT TOP 2 Fitness_Test.Member_ID, Fitness_Test.Date
FROM Fitness_Test
ORDER BY Fitness_Test.Date DESC;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Poobear1929]

Thanks, I plugged both of those into my query, but it just returns one date. I need to have two date colums, one for the most current date, then the second at the next date after the current date. How would I rewrite these to give me Member ID,Current Date, Second Date?

Thank You

 

[PHV]

SELECT A.Member_ID, Max(A.Date) AS MaxOfDate, Max(B.Date) AS SecondDate
FROM Fitness_Test A LEFT JOIN Fitness_Test B
ON (A.Member_ID = B.MemberID) AND (A.Date > B.Date)
GROUP BY A.Member_ID;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244