Most Recent Record for Each User

partypete

Hello,

I want to get each user's most recent record based on the time_in column below:

time_username      time_in         time_out
bob                2005-05-22      2005-05-24
steve              2005-08-28      2005-08-29
steve              2006-11-01      2006-11-04
janet              2005-12-11      2005-12-12
bob                2006-11-21      2006-11-22
janet              2005-04-01      2005-04-05
steve              2005-09-19      2005-09-21
bob                2006-10-03      2006-10-04
steve              2006-01-12      2006-01-15
steve              2005-02-17      2005-02-18

Using the above data would then yield the following results:

bob                2006-11-21        2006-11-22
steve              2006-11-01        2006-11-04
janet              2005-12-11        2005-12-12

What would be the best way to request the record with the most recent time_in date for each user?

Thanks,
Peter

NogDog

I'm not sure if the MAX() function works with dates/times, but it's worth a try:

SELECT time_username, MAX(time_in), MAX(time_out) FROM table_name GROUP BY time_username

partypete

Hey NogDog, thanks for your help again on my other post.

I was testing with MAX() earlier but found it returned only the max value of that column and did not return the entire record which the max value belonged to. I need the entire corresponding record. The table in my example was just a simple example, there would actually be more columns with needed information. So simply using MAX() doesn't work.

For example, if you used this query:

SELECT time_username, MAX(time_in), time_out FROM table_name GROUP BY time_username

You would end up with:

bob                2006-11-21        2005-05-24
steve              2006-11-01        2005-08-29
janet              2005-12-11        2005-12-12

In which case bob and steve's time_out would be incorrect. As would be other columns.

I need to get the "entire" most recent record for each user. Really sounds like it should be a simple task huh? :-)

Thanks,
Peter

Lars_Berg

select time_username ,
          time_in,
          time_out
  from t as q
 where time_in in 
  (select max(time_in)
     from t 
   where t.time_username = q.time_username)