CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Sashank Allamraju
Sashank Allamraju

Reputation: 53

What SQL Server (T-SQL) Query can fetch this result?

I have a dataset with 40,000 records. The format is as shown below.

Title  | Publication Date
-------|-----------------
Book-A |   2016-10-20
Book-A |   2017-08-14
Book-B |   2016-09-22
Book-B |   2017-03-16
Book-B |   2017-11-26
Book-C |   2016-06-13
Book-C |   2017-04-15
Book-C |   2016-11-23
Book-C |   2017-12-28

I want the SQL query to return unique book titles with the latest publication date (like below)

Title  | Publication Date
-------|-----------------
Book-A |   2017-08-14
Book-B |   2017-11-26
Book-C |   2017-12-28

What will be the T-SQL query to achieve this?

Upvotes: 0

Views: 46

Answers (3)

Ali Azam
Ali Azam

Reputation: 2115

Use aggregate funcion MAX() like

CREATE TABLE Table1(
    Title [nvarchar](50) NULL,
    PublicationDate [datetime] NULL
)

Insert into Table1 values('Book-A', '2016-10-20')
Insert into Table1 values('Book-A', '2017-08-14')
Insert into Table1 values('Book-A', '2016-09-22')
Insert into Table1 values('Book-B', '2017-03-16')
Insert into Table1 values('Book-B', '2017-11-26')
Insert into Table1 values('Book-C', '2016-06-13')
Insert into Table1 values('Book-C', '2017-04-15')
Insert into Table1 values('Book-C', '2016-11-23')
Insert into Table1 values('Book-C', '2017-12-28')

Select Title, format(Max(PublicationDate), 'yyyy-MM-dd') as [Publication Date]
from Table1 group by Title

Drop Table Table1

Upvotes: 0

ta.speot.is
ta.speot.is

Reputation: 27214

SELECT [Title],
       Max([Publication Date]) [Publication Date]
FROM   [DataSet]
GROUP  BY [Title]

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

One option uses a rank analytic function:

SELECT Title, [Publication Date]
FROM
(
    SELECT Title, [Publication Date],
        RANK() OVER (PARTITION BY Title ORDER BY [Publication Date] DESC) rank
    FROM yourTable
) t
WHERE rank = 1
ORDER BY Title;

This approach would capture mutliple records for a given title should a tie occur for the latest date. If you insist on a single latest record per title, you can replace RANK with ROW_NUMBER. Better yet, keep RANK and add logic to the ORDER BY clause which breaks the tie.

Upvotes: 0

Related Questions