TOP N Query

Last Post 03 May 2005 11:16 AM by rothjm_SQL. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
rothjm
New Member
New Member

--
03 May 2005 06:11 AM
I have a table with Consumers and Event Dates.
I want to query the latest Event Date for each Consumer.
I tried TOP N but that gave me just the TOP Event Date (what I sorted with).
I want the latest Event Date per Consumer.

Here is a sample of what I have:
TABLE1

TestCons1 1/3/2005
TestCons2 1/12/2005
TestCons1 1/25/2005
TestCons1 1/10/2005
TestCons2 1/15/2005
TestCons2 1/8/2005

Here is what I want for a result:

TestCons1 1/25/2005
TestCons2 1/15/2005

Do I need some kind of nested Top N Select with two sorts?
Thanks for helping the rook out. I did search but couldn't find an example.
TIA
nosepicker
New Member
New Member

--
03 May 2005 10:29 AM
I believe this should give you what you want:

SELECT consumers, MAX(event_date)
FROM table1
GROUP BY consumers

This assumes that event_date is of datetime format. If not, then you will have to convert it to datetime:

SELECT consumers, MAX(CONVERT(datetime, event_date))
FROM #table1
GROUP BY consumers
rothjm_SQL
New Member
New Member

--
03 May 2005 11:16 AM
Thank you very much for your insight.
This got me going!
You are not authorized to post a reply.

Acceptable Use Policy