Sometimes you need to return maximum (or minimum value) for more than one column. In my example I’m selecting book list with highest and most recent rating for each book. This query will work with MS SQL Server 2005.

CREATE TABLE Books(
	BookID int NOT NULL,
	Title varchar(50) NOT NULL,
) ON [PRIMARY]
GO

CREATE TABLE Ratings(
    RatingID int NOT NULL,
    BookID int NULL,
    Rating int NULL,
    DateAdded datetime NULL
) ON [PRIMARY]
GO

INSERT Books VALUES (1, 'Book A')
INSERT Books VALUES (2, 'Book B')

INSERT Ratings VALUES (1, 1, 4, '2007-11-01')
INSERT Ratings VALUES (1, 1, 4, '2007-10-27')
INSERT Ratings VALUES (1, 2, 4, '2007-10-25')
INSERT Ratings VALUES (1, 2, 5, '2007-10-25')
GO

WITH RatingsV AS
(
	SELECT B.Title, R.Rating, R.DateAdded,
		ROW_NUMBER() OVER (
			PARTITION BY B.BookID ORDER BY R.Rating DESC, R.DateAdded DESC
		) as Pos
	FROM Books B
	INNER JOIN Ratings R ON R.BookID = B.BookID
)
SELECT *
FROM RatingsV
WHERE Pos = 1
GO

The following data is returned when query is run:

Title              Rating      DateAdded               Pos
------------------ ----------- ----------------------- -------
Book A             4           2007-11-01 00:00:00.000 1
Book B             5           2007-10-25 00:00:00.000 1