SQL PUZZLE | The Distinct and MAX Value Puzzle – A SINGLE SELECT Challenge | SQL Interview Question

In this puzzle you have to find out the unique Ids present in the table. You also have to find out the SUM of Max values of vals columns for each Id and RId. For more details please see the sample input and expected output.

Sample Input

Id rID Vals
121 9 1
121 9 8
122 9 14
122 9 0
122 9 1
123 9 1
123 9 2
123 9 10

Expected Output

Distinct_Ids rid TotalOfMaxVals
3 9 32

Script – DDL and INSERT Sample Data

 ```-- CREATE TABLE MyTabel ( Id INT , rID INT , Vals INT ) GO INSERT INTO MyTabel VALUES (121, 9, 1) ,(121, 9, 8) ,(122, 9, 14) ,(122, 9, 0) ,(122, 9, 1) ,(123, 9, 1) ,(123, 9, 2) ,(123, 9, 10) GO SELECT * FROM MyTabel GO -- ```

SOLUTION – 1 | A SINGLE SELECT SOLUTION 🙂

 ```-- SELECT TOP 1 SUM(COUNT(DISTINCT Id)) OVER() Distinct_Ids ,rid ,SUM(MAX(Vals)) OVER() TotalOfMaxVals FROM MyTabel GROUP BY Id,rid -- ```

Output – 1

 ```-- Distinct_Ids rid TotalOfMaxVals ------------ ----------- -------------- 3 9 32 (1 row affected) -- ```

SOLUTION – 2 – A Two SELECT Solution

 ```-- ;WITH CTE AS ( SELECT *,ROW_NUMBER() OVER (PARTITION BY Id,rid ORDER BY Vals DESC) rnk FROM MyTabel ) SELECT COUNT(DISTINCT Id) AS Distinct_Ids, rid, SUM(Vals) AS TotalOfMaxVals FROM CTE WHERE rnk = 1 GROUP BY rid -- ```

Output – 2

 ```-- Distinct_Ids rid TotalOfMaxVals ------------ ----------- -------------- 3 9 32 (1 row affected) -- ```

SOLUTION – 3 – A Two SELECT Solution using CROSS APPLY

 ```-- SELECT COUNT(DISTINCT Id) AS Distinct_Ids, RId, SUM(Maxy) AS TotalOfMaxVals FROM ( SELECT DISTINCT Id,RId FROM MyTabel )x CROSS APPLY ( SELECT MAX(Vals) Maxy FROM MyTabel a WHERE x.Id = a.Id AND x.rID = a.rID )z GROUP BY RId -- ```

Output – 3

 ```-- Distinct_Ids RId TotalOfMaxVals ------------ ----------- -------------- 3 9 32 (1 row 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/