Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2003

    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.

    SELECT *
    FROM tblPublishedBooks
    WHERE (something here to indicatate that only the record with the max(dateTimeStamp) should be returned for each author)
    GROUP BY authorID

    Thanks for any tips!

  2. #2
    Join Date
    Nov 2002

    Re: Selecting all fields in a record with most recent date field

    What happens if there are more than 1 with the same date?

    What would you want to do then?

    USE Northwind
    SELECT * FROM Orders
    WHERE OrderDate IN (SELECT MAX(OrderDate) FROM Orders)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    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

  4. #4
    Join Date
    Jul 2002
    Village, MD
    What about this?

    create table #test(id int,auid int,pdate datetime)
    insert #test values(1,1,'01.01.2003')
    insert #test values(2,1,'01.11.2003')
    insert #test values(3,2,'02.01.2003')
    insert #test values(4,2,'02.11.2003')
    insert #test values(5,3,'01.01.2003')
    insert #test values(6,3,'02.01.2003')
    insert #test values(7,3,'03.01.2003')
    select *
    from #test t
    where pdate in(select max(pdate) from #test where auid=t.auid)

    You, also, can add grouping if it needs (two max dates for the same author).
    Last edited by snail; 10-16-03 at 16:11.

  5. #5
    Join Date
    Feb 2003
    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.

    Thanks again!

  6. #6
    Join Date
    Feb 2003
    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.

    SELECT *
    FROM tblUserActions a INNER JOIN ( select uacEntityId, uacSessionId, dateStamp=max(uacDateStamp)
    FROM tblUserActions
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts