Rookie Question

Hi,

I'm trying to read the most recent entries in a table of data.

Its fairly straightforward because there is a timestamp saved when I write into the table.

For a table with the following columns

Username, description, location, timestamp, result1, result2

I can select the max timestamp for a username and it will return the most recent date/time that an entry was written to the table for that particular user.  No problems with that.

However, what I really want is to select the most recent timestamp for each location for each username; I also need the result1 and result2 fields returned.

Thus for the following table:

user   description   location   timestamp   result1   result2

Jo      Red               Hall        01/01/06      Yes      Yes

Jo      Blue               Hall        01/02/06      Yes      No

Jo      Blue               Deck      01/04/06      No      No

 

I would like a query to return:

Jo      Blue               Hall        01/02/06      Yes      No

Jo      Blue               Deck      01/04/06      No      No

The problem is if I do

Select  max(timestamp) as Tmstp, description, user,   location,   result1,  result2 from tblSomeTble

I will get

Jo      Red               Hall        01/01/06      Yes      Yes

Jo      Blue               Hall        01/02/06      Yes      No

Jo      Blue               Deck      01/04/06      No      No

because some of the results are different and thus the SQL will return the most recent entry for each unique combination of  user,   location,   result1 and result2.

I've solved the problem by doing two separate database reads; the first does a simple

Select  max(timestampdescription) as Tmstp, user, location

then I use VB.net to fill a dataset and read these values from the dataset and plug them into a second SQL for a second database read like:

Select  max(timestamp) as Tmstp, description, user,   location,   result1,  result2 from tblSomeTble where timestamp = [timestamp from first sqlread] and user = [user] and location = [location from first sqlread]

it all works fine, but I'm aware its messy and probably not very scaleable to have multiple database reads that are unnecessary.  It must be a commonly encountered problem though?  How do other people handle this? is there a common solution people use?

thanks