Monday, February 18, 2013 2:46 PM
I got a table similar to this:
ID Name last_email last_phone event_timestamp
1 George 01/01/2010 01/01/2011 2/2/2013
2 George 01/01/2012 02/02/2012 1/1/2013
more clients follow
For each client, i'd like to pick up the row with the latest timestamp - so row number 1 in the above case.
I am using MAX(event_timestamp) in the SELECT, and putting all the rest in the GROUP_BY, but that's showing me both rows in results.
I tried MAX on all three date columns and that shows the latest timestamp, BUT that's returning the last_email and last_phone from row 2. - it's mixing data from row 1 and 2
so last_email and last phone do not belong to either an aggregate or in the GROUP BY, what do I do?
Thanks a lot!!!
- Edited by goldfrapp01 Monday, February 18, 2013 2:47 PM added info
Monday, February 18, 2013 2:52 PM
If you are on SQL 2005 or later you can use ROW_NUMBER():
SELECT * FROM( SELECT *,ROW_NUMBER() OVER(PARTITION BY Id ORDER BY event_timestamp DESC) rn FROM dbo.tbl )X WHERE rn = 1;
- Marked As Answer by Iric WenModerator Tuesday, February 26, 2013 8:38 AM
Monday, February 18, 2013 2:53 PM
select * from <<tableName>> where eventdate = (select MAX(Eventdate) from <<tableNAMe>>)
declare @sno table ( sno int , Eventdate date) insert into @sno values (1,'01/01/2013'), (2,'02/01/2013') select * from @sno where eventdate = (select MAX(Eventdate) from @sno)
Hope it Helps!!