Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL SERVER Interview questions, SQL Skills, 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 Multiple Condition Puzzle ]
Puzzle Statement
- We have a table called “Groups”
- I need to pick the group_no’s who has group_name both TEST1 and TEST2 and Active should be 1. In this case I need 100 and 300 as this both group_no contains TEST1 and TEST2 and are active.
- Here i don’t get 200 because it has both groupnames but for the 4th record its active status is 0.
- Here i dont get 400 because it has only TEST1 but dont have a record with groupname TEST2.
- Please check out the sample input and expected output for details.
Sample Input
| Group_id | Group_no | Group_name | Active |
| 1 | 100 | TEST1 | 1 |
| 2 | 100 | TEST2 | 1 |
| 3 | 200 | TEST1 | 1 |
| 4 | 200 | TEST2 | 0 |
| 5 | 300 | TEST1 | 1 |
| 6 | 300 | TEST2 | 1 |
| 7 | 400 | TEST1 | 1 |
Expected output
| Group_id | Group_no | Group_name | Active |
| 1 | 100 | TEST1 | 1 |
| 2 | 100 | TEST2 | 1 |
| 5 | 300 | TEST1 | 1 |
| 6 | 300 | TEST2 | 1 |
Rules/Restrictions
- Your solution should be should use “SELECT” statement or “CTE”
- Your solution should be generic in nature.
- 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 Groups ( Group_id INT ,Group_no INT ,Group_name VARCHAR(10) ,Active INT ) GO INSERT INTO Groups VALUES (1, 100, 'TEST1', 1), (2, 100, 'TEST2', 1), (3, 200, 'TEST1', 1), (4, 200, 'TEST2', 0), (5, 300, 'TEST1', 1), (6, 300, 'TEST2', 1), (7, 400, 'TEST1', 1) -- |
UPDATE – 23-Apr-2015 – Solution 1
-- ;WITH CTE1 AS ( SELECT Group_no FROM Groups WHERE Active = 1 AND Group_name = 'TEST1' ) , CTE2 AS ( SELECT Group_no FROM Groups WHERE Active = 1 AND Group_name = 'TEST2' ) SELECT g.* FROM CTE1 a INNER JOIN CTE2 b ON a.Group_no = b.Group_no INNER JOIN Groups g on a.Group_no = g.Group_no -- |
Add a comment if you have any other solution in mind. We all need to learn.
Keep Learning
Improving my SQL BI Skills 
select
tbl.Group_id,
tbl.Group_no,
tbl.Group_name
from(
select
*,
COUNT(case when Group_name IN (‘TEST1′,’TEST2’) then 1 end) over (partition by Group_no order by (select null)) as counted
from Groups g
where Active = 1
) tbl
where tbl.counted = 2
LikeLiked by 1 person
with cte as
(
select * from groups where group_name = ‘test1’ and active =1
union all
select * from groups where group_name = ‘test2’ and active =1
),
cte1 as
(
select *, count(group_no) over (partition by group_no order by group_id) as cnt from cte
),
cte2 as
(
select *,max(cnt) over (partition by group_no order by (select 1)) as req1 from cte1
)
select group_id, group_no,group_name, active from cte2
where req1 >1
LikeLiked by 1 person
SELECT g.Group_id,tbl1.Group_no,g.Group_name,tbl1.Active
FROM
(
SELECT Group_id,Group_no,Group_name,Active,
rank() OVER(PARTITION BY Group_no ORDER BY Group_id) AS rnk
FROM groups
)tbl1
inner join Groups g ON tbl1.Group_no=g.Group_no
WHERE rnk=2 and tbl1.Active=1
LikeLike
Hi,
Thank you for the comment.
Best,
Pawan
LikeLike
;with cte as (
select *, count(active) over (partition by group_no order by group_no) as ct from Groups
where Active=1
) select GROUP_ID, group_no, GROUP_NAME, active
from cte where ct=(select count(1) as t from (select distinct group_name from groups) as k)
LikeLike