Tags
Complex SQL Challenges, Complex TSQL Challenge, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Khowal, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, puzzle sql developer, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Interview Questions, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Puzzles wanted, SQL Queries, SQL Quiz, SQL Server Database, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQL Top clause, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, T SQL Puzzles, T-SQL Challenge, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
T-SQL Query | [ The ZIP Grouping Puzzle ]
Puzzle Statement
- We have a table called zip33
- We have group data based on code and then Zip Start & Zip End if Zip Start < Zip End
- Please check out the sample input and expected output for details.
Sample Input
zip | code |
56790000 | A101 |
56800000 | A101 |
56810000 | A101 |
56820000 | A101 |
56890000 | A101 |
56900000 | A102 |
56901111 | A102 |
56901168 | A103 |
56910000 | A102 |
56920000 | A102 |
Expected Output
Code | ZipStart | ZipEnd |
A101 | 56790000 | 56890000 |
A102 | 56900000 | 56901111 |
A102 | 56910000 | 56920000 |
A103 | 56901168 | 56901168 |
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 zip33 (zip varchar(9), code varchar(4)) insert zip33 select '056790000' ,'A101' insert zip33 select '056800000' ,'A101' insert zip33 select '056810000' ,'A101' insert zip33 select '056820000' ,'A101' insert zip33 select '056890000' ,'A101' insert zip33 select '056900000' ,'A102' insert zip33 select '056901111' ,'A102' insert zip33 select '056901168' ,'A103' insert zip33 select '056910000' ,'A102' insert zip33 select '056920000' ,'A102' — |
Update May 6 | Solution 1
-- ;WITH CTE AS ( SELECT * , ROW_NUMBER() OVER (ORDER BY %%Physloc%%) - DENSE_RANK() OVER ( PARTITION BY code ORDER BY zip) rnk FROM zip33 ) SELECT MIN(Code) Code , MIN(zip) ZipStart , MAX(zip) ZipEnd FROM CTE GROUP BY rnk -- |
Add a comment if you have any other solution in mind. We all need to learn.
Keep Learning