How to select a rows based on a Maximum value of a specific column?

Question

I am trying to select a rows based on a Maximum value of a specific column value in SQL server 2008. I need to select the bookname and year based on the maximum edition.

Current table data:

    BookName    Year    Edition
Latest Science  2013    1
Latest Science  2014    2
Latest Science  2015    3
Easy Maths      2014    1
Easy Maths      2015    2
Magic vs Logic  2015    1

Output should be:

BookName    Year    Edition
Latest Science  2015    3
Easy Maths      2015    2
Magic vs Logics 2015    1

I tried but it result only one rows of entire table

SELECT     Book, year, edition
FROM         Book_info
where edition=(select max(edition) from book_info)

Answer 1

I would do it this way:

;WITH cte AS (
 SELECT *, 
 ROW_NUMBER() OVER (PARTITION BY BookName ORDER BY Edition DESC) AS rn
 FROM book_info
)
SELECT * FROM cte
WHERE rn=1

Answer 2

You should look up group by with having clauses.

Here is the entire script per your requirements which returns the results you're looking for.

USE [Test]

--table

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Book](

[Id] [int] IDENTITY(1,1) NOT NULL,

[BookName] [varchar](50) NOT NULL,

[Year] [int] NOT NULL,

[Edition] [int] NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO



USE [Test]

GO

--records

INSERT INTO [dbo].[Book]

([BookName]

,[Year]

,[Edition])

VALUES

('Last Science', 2013 ,1),

('Last Science', 2014 ,2),

('Last Science', 2015 ,3),

('Easy Maths', 2014 ,1),

('Easy Maths', 2015 ,2),

('Magic vs Logic', 2015 ,1)

GO

USE [Test]

GO

--query

SELECT

BookName,

MAX([Year]) AS Year,

MAX(Edition) AS Edition

FROM dbo.Book

GROUP BY

BookName

Order by MAX(Edition) DESC