Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2010
    Posts
    11

    Unanswered: Help with MAX and DISTINCT - MSSQL 2008

    Hi there,

    Trying to query a table to only give me distinct userId and the most recent timestamps.

    --Table: SMSCheck -
    Code:
    INSERT INTO SMSCheck (
        userid, errorcode, smsfound, smssent, timestamp,)
       SELECT 101, 0, 0, 0, '2012-10-08 12:10:29.777' UNION ALL
        SELECT 102, 2, 0, 0, '2012-10-08 11:58:46.127' UNION ALL
        SELECT 103, 0, 1, 1, '2012-10-08 11:49:54.050' UNION ALL
        SELECT 104, 2, 0, 0, '2012-10-08 11:49:23.810' UNION ALL
        SELECT 104, 2, 0, 0, '2012-10-08 11:40:29.777' UNION ALL
        SELECT 109, 2, 0, 0, '2012-10-08 11:40:22.721' UNION ALL
        SELECT 102, 0, 4, 4, '2012-10-08 11:38:46.127' UNION ALL
        SELECT 112, 0, 3, 3, '2012-10-08 11:38:42.120' UNION ALL
        SELECT 101, 0, 3, 3, '2012-10-08 11:29:54.050' UNION ALL
        SELECT 101, 2, 0, 0, '2012-10-08 11:29:54.050'
    What I need back from my query is DISTINCT userID and latest timestamps...LIKE this....

    Code:
     userid, errorcode, smsfound, smssent, timestamp
    101, 0, 0, 0, '2012-10-08 12:10:29.777' 
    102, 2, 0, 0, '2012-10-08 11:58:46.127' 
    103, 0, 1, 1, '2012-10-08 11:49:54.050' 
    104, 2, 0, 0, '2012-10-08 11:49:23.810' 
    109, 2, 0, 0, '2012-10-08 11:40:22.721' 
    112, 0, 3, 3, '2012-10-08 11:38:42.120'
    My failing query...

    Code:
    select distinct(userid), errorcode, smsfound, smssent,  MAX(timestamp) from smscheck
    group by (userid), ErrorCode, timestamp
    order by  MAX(timestamp) desc
    The [smscheck] table goes back ~5 days, and there are multiple userId's (~35). I'm trying to return only distinct UserID's and their most recent checks.

    Thanks!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Code:
    with latestts (userid, [timestamp])
    as
    (select userid, max([timestamp])
    from smscheck
    group by userid)
    
    select s.userid, l.[timestamp], s.errorcode
    from smscheck s join 
      latestts l on s.userid= l.userid and s.[timestamp] = l.timestamp
    Untested, so you may need to tweak, and/or fix syntax errors.

  3. #3
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    You can also try:

    Code:
    with CTE as
    (
        select t.*, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY timestamp DESC) AS RowNum
        from smscheck
    )
    
    select * from CTE
    where RowNum = 1
    Hope this helps.

  4. #4
    Join Date
    Feb 2010
    Posts
    11
    Hi there,

    I think I got this...

    Code:
    SELECT sc.*
    FROM smscheck sc
    INNER JOIN
    	(
    	SELECT userid, max(timestamp) as LastCheckTS
    	FROM smscheck
    	GROUP BY userid
    	)
    smscheckP ON sc.userid = smscheckP.userid AND sc.timestamp = smscheckp.LastCheckTS
    Thanks for the help though!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •