Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Vertical Minimum for each group Puzzle

In this puzzle you have to find minimum for each group after the group in the vertical manner and minimum from all the columns.

Please check the sample input and the expected output.

Sample Input

Id Vals Vals1 Vals2 Vals3
A S1 103 53 3
A S2 45 31 12
A S3 10 21 23
B S3 67 19 8
B S4 20 22 1
B S5 20 1 25

Expected Output

Grp Vals1 Vals2 Vals3
S1 103 53 3
S2 45 31 12
S3 10 21 23
A 10 21 3
S3 67 19 8
S4 20 22 1
S5 20 1 25
B 20 1 1
Minimum OF ALL GROUPS 10 1 1

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

--

CREATE TABLE GroupsAndTotalData
(
    Id VARCHAR(10),
    Vals VARCHAR(50),
    Vals1 INT,
    Vals2 INT,
    Vals3 INT
) 
GO  

INSERT INTO GroupsAndTotalData VALUES
('A','S1',103,53,3),
('A','S2',45,31,12),
('A','S3',10,21,23),
('B','S3',67,19,8) ,
('B','S4',20,22,1) ,
('B','S5',20,1,25) 
GO

SELECT * FROM GroupsAndTotalData
GO

--

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

SOLUTION – 1

--


;WITH CTE AS
(
    SELECT Id,Vals,MIN(Vals1) Vals1, MIN(Vals2) Vals2,MIN(Vals3) Vals3
    FROM GroupsAndTotalData
    GROUP BY ROLLUP(Id,Vals)        
)
SELECT 
	ISNULL(CASE ISNULL(Vals,'') WHEN '' THEN Id ELSE Vals END , 'Minimum OF ALL GROUPS' ) AS Grp,
	Vals1,
	Vals2,
	Vals3      
FROM CTE 



--

Output-1

--                            


Grp                                                Vals1       Vals2       Vals3
-------------------------------------------------- ----------- ----------- -----------
S1                                                 103         53          3
S2                                                 45          31          12
S3                                                 10          21          23
A                                                  10          21          3
S3                                                 67          19          8
S4                                                 20          22          1
S5                                                 20          1           25
B                                                  20          1           1
Minimum OF ALL GROUPS                              10          1           1

(9 rows 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 Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ 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/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com