SQL Puzzle | The DISTINCT COUNT Without DISTINCT Keyword & With SINGLE SELECT | Advanced SQL

This problem was asked to me and Vaibhav by one of my fellow Architect. He asked whether can we can get the distinct count using the partition function. I told them that with partition we cannot use DISTINCT keyword. Vaibhav came with an interesting idea of using dense rank there and that worked like a charm. Basically in this puzzle you have to find out the distinct count of sub products for each product in a single select with all the existing data. Also better if you don’t use the distinct keyword. For more details please see the sample input and expected output.

Sample Input

Id Prod SubProd Dt
1 a sub1 2018-06-27 17:02:30.050
2 a sub1 2018-06-25 17:02:30.050
3 a sub2 2018-06-24 17:02:30.050
4 b sub3 2018-06-22 17:02:30.050
5 c sub4 2018-06-26 17:02:30.050
6 c sub4 2018-06-25 17:02:30.050
7 c sub4 2018-06-24 17:02:30.050

Expected Output

Id Prod SubProd Dt DistinctCount
1 a sub1 2018-06-27 17:02:30.050 2
2 a sub1 2018-06-25 17:02:30.050 2
3 a sub2 2018-06-24 17:02:30.050 2
4 b sub3 2018-06-22 17:02:30.050 1
5 c sub4 2018-06-26 17:02:30.050 1
6 c sub4 2018-06-25 17:02:30.050 1
7 c sub4 2018-06-24 17:02:30.050 1

Script – DDL and INSERT Sample Data

 ```-- CREATE TABLE DISTINCTProducts ( Id INT ,Prod VARCHAR(10) ,SubProd VARCHAR(10) ,Dt DATETIME ) GO INSERT INTO DISTINCTProducts VALUES (1,'a','sub1',GETDATE()), (2,'a','sub1',GETDATE()-2), (3,'a','sub2',GETDATE()-3), (4,'b','sub3',GETDATE()-5), (5,'c','sub4',GETDATE()-1), (6,'c','sub4',GETDATE()-2), (7,'c','sub4',GETDATE()-3) GO SELECT * FROM DISTINCTProducts GO -- ```

SOLUTION by Vaibhav Goel | Using DENSE_RANK

 ```-- SELECT a.*, DENSE_RANK() OVER(PARTITION BY Prod ORDER BY SubProd DESC) + DENSE_RANK() OVER(PARTITION BY Prod ORDER BY SubProd) - 1 DistinctCount FROM DISTINCTProducts a -- ```

OUTPUT – 1

 ```-- Id Prod SubProd Dt DistinctCount ----------- ---------- ---------- ----------------------- -------------------- 1 a sub1 2018-06-27 17:02:30.050 2 2 a sub1 2018-06-25 17:02:30.050 2 3 a sub2 2018-06-24 17:02:30.050 2 4 b sub3 2018-06-22 17:02:30.050 1 5 c sub4 2018-06-26 17:02:30.050 1 6 c sub4 2018-06-25 17:02:30.050 1 7 c sub4 2018-06-24 17:02:30.050 1 (7 rows affected) -- ```

SOLUTION – 2 | using CROSS APPLY

 ```-- SELECT a.*,x.DistinctSubs FROM DISTINCTProducts a CROSS APPLY ( SELECT COUNT(DISTINCT SubProd) DistinctSubs FROM DISTINCTProducts WHERE a.Prod = Prod )x -- ```

OUTPUT – 2

 ```-- Id Prod SubProd Dt DistinctSubs ----------- ---------- ---------- ----------------------- ------------ 1 a sub1 2018-06-27 17:02:30.050 2 2 a sub1 2018-06-25 17:02:30.050 2 3 a sub2 2018-06-24 17:02:30.050 2 4 b sub3 2018-06-22 17:02:30.050 1 5 c sub4 2018-06-26 17:02:30.050 1 6 c sub4 2018-06-25 17:02:30.050 1 7 c sub4 2018-06-24 17:02:30.050 1 (7 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/