SQL PUZZLE | Find Maximum value record for each Id? | A SINGLE SELECT Interview Question

In this puzzle you have to find the maximum value for each Id and then get the Item for that Id and Maximum value. The Challenge is to do that in a SINGLE SELECT. Please check out sample input and expected output.

Sample Input

ID Item Vals
1 a1 15
1 a2 20
1 a3 90
2 q1 10
2 q2 40
2 q3 60
2 q4 30
3 q5 20

Expected output

ID Item Vals
2 q3 60
1 a3 90
3 q5 20

Script – DDL and INSERT sample data

 ```-- CREATE TABLE TestMaximum ( [ID] INT ,[Item] VARCHAR(20) ,Vals INT ) INSERT TestMaximum VALUES (1, 'a1',15), (1, 'a2',20), (1, 'a3',90), (2, 'q1',10), (2, 'q2',40), (2, 'q3',60), (2, 'q4',30), (3, 'q5',20); SELECT * FROM TestMaximum GO -- ```

SOLUTION – 1

 ```-- SELECT TOP 1 WITH TIES [ID], [Item], Vals FROM TestMaximum ORDER BY ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY Vals DESC) -- ```

OUTPUT – 1

 ```-- ID Item Vals ----------- -------------------- ----------- 2 q3 60 1 a3 90 3 q5 20 (3 rows affected) -- ```

Enjoy 🙂

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 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/