SQL Puzzle | The Twin Condition Puzzle | SQL Server Interview Question

In the puzzle you have to find records where records have same length what the length records A has. In below data record A has length 5. So we have to first find the length of record A and then we have to find records with the same length what A has. In this case we need record D as the output. Please check the sample input and the expected output.

Notes –
1. We do not need A in the output.
2. The limitation is that we can only use a single SELECT for the puzzle.

Sample Input

Id Name Length
1 A 5
2 B 3
3 C 4
4 D 5

Expected Output

Id Name Length
1 D 5

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE FindRecordsWithALength ( Id INT ,[Name] VARCHAR(10) ,[Length] INT ) GO INSERT INTO FindRecordsWithALength VALUES (1,'A',5), (2,'B',3), (3,'C',4), (4,'D',5) GO SELECT * FROM FindRecordsWithALength GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

SOLUTION – 1 | This solution was given by my friend ASHUTOSH

 ```-- SELECT b.* FROM FindRecordsWithALength a INNER JOIN FindRecordsWithALength b ON a.Name = 'A' AND a.Length = b.Length AND a.Id != b.Id GO -- ```

Output-1

 ```-- Id Name Length ----------- ---------- ----------- 4 D 5 (1 row affected) -- ```

Add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn.

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/