SQL Query Help

[Logan]

I have two tables, say USER and PAGE. USER has a USER_ID field, and PAGE has a PAGE_DATE field. For each USER record, there can be multiple PAGE records (a 1-to-many relationship).

How can I pull out a single record set that has the most recent PAGE record (based on date) for each USER record?

Anyone?

 

[Inka]

I have two tables, say USER and PAGE. USER has a USER_ID field, and PAGE has a PAGE_DATE field. For each USER record, there can be multiple PAGE records (a 1-to-many relationship).

How can I pull out a single record set that has the most recent PAGE record (based on date) for each USER record?

Anyone?

Robert,

If you use SQL Server or Access, try this:

SELECT  P1.* FROM PAGE AS P1
WHERE (((P1.PAGE_DATE)
 In (select top 1 P2.PAGE_DATE  from 
PAGE P2 WHERE P2.UserID = P1.UserID 
ORDER BY PAGE_DATE DESC)));

Another solution could be

SELECT Max(PAGE.PAGE_DATE) AS MaxOfPAGE_DATE, PAGE.USERID
FROM PAGE
GROUP BY PAGE.USERID

There is probably a more efficient solution although these two were the first thing I thought. The second query needs to be modified in order to include more fields

It would be great to have a PAGE_ID or something like that because the PAGE_DATE field maybe will not be accurate enough. Maybe a timestamp field??

Good luck