Simple query has me stumped!

Dev Shed Forums Sponsor:

December 7th, 2012, 02:00 PM

BishBishop

Registered User

Join Date: Dec 2012

Posts: 2

Time spent in forums: 33 m 55 sec
Reputation 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.

December 7th, 2012, 08:06 PM

spacebar208

Contributing User

Join Date: Apr 2012

Location: spaceBAR Central

Posts: 191

Time spent in forums: 2 Days 10 h 55 sec
Reputation Power: 41

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

December 8th, 2012, 04:45 AM

BishBishop

Registered User

Join Date: Dec 2012

Posts: 2

Time spent in forums: 33 m 55 sec
Reputation Power: 0

Thanks so much for your reply, appreciated. Worked great once I had aliased the subquery.

Bish.

December 8th, 2012, 05:21 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,685

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 59 m 5 sec
Reputation Power: 284

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

Simple query has me stumped!

Developer Shed Advertisers and Affiliates