Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
pick the event_timestamp for a customer

Answered pick the event_timestamp for a customer

  • Monday, February 18, 2013 2:46 PM
     
     
    Sign In to Vote
    0

    Hi everyone,

    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!!!

    Dimitar


    • Edited by goldfrapp01 Monday, February 18, 2013 2:47 PM added info
    •  
     

All Replies

  • Monday, February 18, 2013 2:52 PM
     
     Answered Has Code
    Sign In to Vote
    0

    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;

     
  • Monday, February 18, 2013 2:53 PM
     
     Answered Has Code
    Sign In to Vote
    0
     
    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!!


    • Edited by Stan210 Monday, February 18, 2013 2:54 PM
    • Marked As Answer by Iric WenModerator Tuesday, February 26, 2013 8:38 AM
    •