Tags
Complex SQL Challenges, Complex TSQL Challenge, Dynamic Analysis Expressions, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER Puzzles, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
T-SQL Query | [ The Version Puzzle ]
Original puzzle link – https://www.simple-talk.com/blogs/2005/10/28/a-sql-puzzle-for-you-all/
The puzzle is simple. We have a version table that holds all the version of all our products that we release. The version number is represented with four numbers a,b,c and d where a is the major version and the other letters represent which sub version it is. So the puzzle is to write a query to find the latest build for each major version. So in result from the data bellow should be.
a | b | c | d |
1 | 2 | 2 | 4 |
2 | 3 | 1 | 7 |
3 | 2 | 1 | 5 |
You are only allowed to use one SQL statement and definitely no cursers. Also no solutions that use any strings!!!!!. ALSO a, b, c or d could all potentially be Int32.MaxValue so make sure your solutions don’t overflow!!!!!!!
Sample Input
a | b | c | d |
1 | 1 | 1 | 23 |
1 | 2 | 1 | 1 |
1 | 2 | 2 | 4 |
2 | 1 | 1 | 1 |
3 | 1 | 1 | 2 |
2 | 1 | 2 | 8 |
2 | 1 | 3 | 4 |
2 | 2 | 1 | 2 |
2 | 2 | 3 | 1 |
2 | 2 | 4 | 6 |
2 | 3 | 1 | 7 |
3 | 2 | 1 | 5 |
Expected output
a | b | c | d |
1 | 2 | 2 | 4 |
2 | 3 | 1 | 7 |
3 | 2 | 1 | 5 |
Rules/Restrictions
- The solution should be should use “SELECT” statement or “CTE”.
- Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com
Script
Use the below script to generate the source table and fill them up with the sample data.
---- CREATE TABLE VersionPuzzle ( a int, b int, c int, d int ) INSERT INTO VersionPuzzle(a, b, c, d) SELECT 1,1,1,23 INSERT INTO VersionPuzzle(a, b, c, d) SELECT 1,2,1,1 INSERT INTO VersionPuzzle(a, b, c, d) SELECT 1,2,2,4 INSERT INTO VersionPuzzle(a, b, c, d) SELECT 2,1,1,1 INSERT INTO VersionPuzzle(a, b, c, d) SELECT 3,1,1,2 INSERT INTO VersionPuzzle(a, b, c, d) SELECT 2,1,2,8 INSERT INTO VersionPuzzle(a, b, c, d) SELECT 2,1,3,4 INSERT INTO VersionPuzzle(a, b, c, d) SELECT 2,2,1,2 INSERT INTO VersionPuzzle(a, b, c, d) SELECT 2,2,3,1 INSERT INTO VersionPuzzle(a, b, c, d) SELECT 2,2,4,6 INSERT INTO VersionPuzzle(a, b, c, d) SELECT 2,3,1,7 INSERT INTO VersionPuzzle(a, b, c, d) SELECT 3,2,1,5 ----------- |
UPDATE – 20-Apr-2015 – Solution 1 & 2
-- --Solution 1 SELECT yr.a ,yr.b,yr.c,yr.d FROM ( SELECT r.a,Max(rnk) rnk FROM ( SELECT a , ROW_NUMBER() OVER (PARTITION BY a ORDER BY a,b,c,d) rnk FROM Version ) r GROUP BY r.a ) gr LEFT JOIN ( SELECT a ,b,c,d , ROW_NUMBER() OVER (PARTITION BY a ORDER BY a,b,c,d) rnk FROM Version ) yr on gr.a = yr.a AND gr.rnk = yr.rnk --Solution 2 SELECT a , b, c, d FROM ( SELECT a , b, c, d , ROW_NUMBER() OVER (PARTITION BY a ORDER BY b DESC,c DESC,d DESC ) rnk FROM Version ) A WHERE rnk = 1 -- |
Add a comment if you have any other solution in mind. We all need to learn.
Keep Learning