Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Latest Product Record – A SINGLE Select | SQL Interview Question

In this puzzle you have to find out the latest record for each Product name. The Challenge is to do this in a SELECT select keyword. For more details please see the sample input and expected output.

Sample Input

Id PName Date
1 P1 2018-06-15 12:29:36.000
2 P1 2018-06-24 12:29:36.000
3 P1 2018-06-25 12:29:36.000
4 P1 2018-06-23 12:30:46.637
5 P2 2018-03-17 12:30:13.507
6 P2 2017-08-29 12:30:13.507
7 P2 2017-08-19 12:30:13.507
8 P3 2018-06-25 12:31:00.797

Expected Output

Id PName Date
3 P1 2018-06-25 12:29:36.000
5 P2 2018-03-17 12:30:13.507
8 P3 2018-06-25 12:31:00.797

Script – DDL and INSERT Sample Data

--

CREATE TABLE LatestProducts
(
	 Id INT 
	,PName VARCHAR(10)
	,[Date] DATETIME
)
GO

INSERT INTO LatestProducts VALUES
(1,'P1','2018-06-15 12:29:36.000'),
(2,'P1','2018-06-24 12:29:36.000'),
(3,'P1','2018-06-25 12:29:36.000'),
(4,'P1','2018-06-23 12:30:46.637'),
(5,'P2','2018-03-17 12:30:13.507'),
(6,'P2','2017-08-29 12:30:13.507'),
(7,'P2','2017-08-19 12:30:13.507'),
(8,'P3','2018-06-25 12:31:00.797')
GO

SELECT * FROM LatestProducts
GO

--

SOLUTION – 1

--

SELECT TOP 1 WITH TIES * FROM LatestProducts
ORDER BY ROW_NUMBER() OVER (PARTITION BY PName ORDER BY [Date] DESC)

--

OUTPUT – 1

--

Id          PName      Date
----------- ---------- -----------------------
3           P1         2018-06-25 12:29:36.000
5           P2         2018-03-17 12:30:13.507
8           P3         2018-06-25 12:31:00.797

(3 rows affected)

--

SOLUTION – 2

--

SELECT Id,PName,[Date] 
FROM 
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY PName ORDER BY [Date] DESC) rnk FROM LatestProducts
)z WHERE rnk = 1

--

OUTPUT – 2

--

Id          PName      Date
----------- ---------- -----------------------
3           P1         2018-06-25 12:29:36.000
5           P2         2018-03-17 12:30:13.507
8           P3         2018-06-25 12:31:00.797

(3 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com