#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0

    Simple query has me stumped!


    Guys, it's 8pm and I'm been beating my head against a wall for a while, any chance someone can help me with the simplest thing?

    I have a table which stored user ids, event dates and event descriptions, as follows:

    userid eventdate eventdesctiption
    1 2012-01-01 00:00:00 a
    1 2012-01-02 00:00:00 b
    1 2012-01-03 00:00:00 c
    1 2012-01-04 00:00:00 d
    1 2012-01-05 00:00:00 e
    1 2012-01-06 00:00:00 f
    2 2012-02-01 00:00:00 a
    2 2012-02-02 00:00:00 b
    2 2012-02-03 00:00:00 c
    2 2012-02-04 00:00:00 d
    2 2012-02-05 00:00:00 e
    2 2012-02-06 00:00:00 f


    What I want to select is the latest event for each user, ie:

    userid eventdate eventdesctiption
    1 2012-01-06 00:00:00 f
    2 2012-02-06 00:00:00 f


    so:

    select userid, eventdesc
    from testdb
    where eventdate (is the last one for each user)


    Any help would be really great, thanks in advance.

    PS, sorry for formatting, forum wouldn't allow me to post screenies.
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    Try this:
    Code:
    select userid, eventdate, eventdesc
     from  ( select userid, eventdate, eventdesc,
                    row_number() over( partition by userid order by eventdate desc ) as rownum
              from  testdb )
    where  rownum  =  1
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0
    Thanks so much for your reply, appreciated. Worked great once I had aliased the subquery.

    Bish.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Other possible solutions:
    Code:
    select t1.userid, 
           t1.eventdate, 
           t1.eventdesc
    from the_table t1
    where eventdate = (select max(eventdate)
                       from the_table t2
                       where t2.userid = t1.userid);
    
    select t1.userid, 
           t1.eventdate, 
           t1.eventdesc
    from the_table t1
      join (select userid, max(eventdate)
            from the_table
            group by userid) t2
        on t1.userid = t2.userid;
    But I wouldn't be surprised if spacebar's solution was more efficient (as only a single scan over the table is needed).
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo