Select maximum value from multiple columns with SQL Server 2005
Posted by Max | Posted in sql | Posted on 01-11-2007
0
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
