Unanswered: Selecting all fields in a record with most recent date field
Hi eveyone, I have what may be a fairly simple question about how to write a SQL query. Let's pretend I have a table with 10 fields relating to books published. These fields would include a reference to the key id of each individual author, the date of publication, the title, a description of the book, etc..
If I wanted to select all information about the most recent book published by each author how would I do that?
I know that using max() will return the most recent date, but this only seems to work as part of the SELECT and not in the WHERE.
WHERE (something here to indicatate that only the record with the max(dateTimeStamp) should be returned for each author)
GROUP BY authorID
select * from tblPublishedBooks p
inner join (select authorid, title, lastpublished=max(datetimestamp)
from tblPublishedBooks group by authorid, title) b
on p.authorid = b.authorid
and p.title = b.title
and p.datetimestamp = b.lastpublished
Thanks guys. I actually just used the publisher / books as an example because I figured explaining my unique situation would have been unecessarily confusing.
There isn't going to be the chance that 2 identical date values will appear because of the way the application is designed. This is in actuality for a security implementation--the application is designed in a way that a user couldn't be preforming 2 actions within the application at the same time.
I'm going to give these suggestions a try and let you know how I ended up writing the query.
Thaks to everyone who contributed an answer. Ended up using a slight variation on ms_sql_dba's query to suit the purpose of my application.
The query is posted below just in case anyone wants to check it out.
Thanks again for eveyones help.
FROM tblUserActions a INNER JOIN ( select uacEntityId, uacSessionId, dateStamp=max(uacDateStamp)
WHERE (uacLogoutAction <> 1) AND (datediff(minute, uacDateStamp, getDate()) < 10)
GROUP BY uacEntityId, uacSessionId) b
ON a.uacSessionId = b.uacSessionId
and a.uacEntityId = b.uacEntityId
and a.uacdateStamp = b.dateStamp