其他列相等时检索最新行的SQL查询

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

这只适用于这种特殊情况,因为 全部的

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

把它修好了

    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

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)

这会得到你想要的,但我知道这不是你想要的。

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

试试这个(没有数据可以测试,但应该可以):

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