SQL Query Retrieving Latest Row When Other Columns Are Equal

Question

Hello,
I'm having trouble figuring out the SQL statement to retrieve a specific set of data. Where all columns are equal except the last update date, I want the most recent. For example.

Book    Author   Update
John    Foo      1/21/2010
John    Foo      1/22/2010
Fred    Foo2     1/21/2010
Fred    Foo2     1/22/2010

What's the query that retrieves the most recent rows? That is, the query that returns:

Book    Author   Update
John    Foo      1/22/2010
Fred    Foo2     1/22/2010

TIA,
Steve

Answer 1

SELECT
    book,
    author,
    MAX(update)
FROM
    My_Table
GROUP BY
    book,
    author

This only works in this particular case because all of the other columns have the same value. If you wanted to get the latest row by book, but where the author (or some other column that you will retrieve) might be different then you could use:

SELECT
    T.book,
    T.author,
    T.update
FROM
    (SELECT book, MAX(update) AS max_update FROM My_Table GROUP BY book) SQ
INNER JOIN My_Table T ON
    T.book = SQ.book AND
    T.update = SQ.max_update

Answer 2

Fixed it up

    DROP TABLE #tmpBooks  
    CREATE TABLE #tmpBooks  
    (
        Book    VARCHAR(100),
        Author  VARCHAR(100),
        Updated DATETIME
    )

    INSERT INTO #tmpBooks VALUES ('Foo', 'Bar', '1/1/1980')
    INSERT INTO #tmpBooks VALUES ('Foo', 'Bar', '1/1/1990')
    INSERT INTO #tmpBooks VALUES ('Foo', 'Bar', '1/1/2000')
    INSERT INTO #tmpBooks VALUES ('Foo', 'Bar', '1/1/2010')
    INSERT INTO #tmpBooks VALUES ('Foo2', 'Bar2', '1/1/1980')
    INSERT INTO #tmpBooks VALUES ('Foo2', 'Bar2', '1/1/1990')
    INSERT INTO #tmpBooks VALUES ('Foo2', 'Bar2', '1/1/2000')

    SELECT   Book, Author, Max(Updated) as MaxUpdated
       FROM  #tmpBooks
       GROUP BY Book, Author

Results:

Book            Author          MaxUpdated
--------------- --------------- -----------------------
Foo             Bar             2010-01-01 00:00:00.000
Foo2            Bar2            2000-01-01 00:00:00.000

(2 row(s) affected)

Answer 3

This will get what you asked for, but something tells me it's not what you want.

SELECT Book, Author, MAX(Update)
  FROM BookUpdates
 GROUP BY Book, Author

Is there more to the table schema?

Answer 4

Try this (don't have the data to test on but it should work):

SELECT
    bu.Book,
    bu.Author
FROM
    BookUpdates bu
        JOIN
    (SELECT MAX(Updated) as Date FROM BookUpdates) max
WHERE
    bu.Updated = max.Date;