SQL Puzzle | Create 0 – 1 Flag ( 0 – Last ROW) | THE SINGLE SELECT Interview Question

In this puzzle you have to generate a new column called Flagger The criteria is for each Vals last row the flagger should be 0 all else is 1.

Please check the sample input and the expected output.

Sample Input

Expected Output

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

--

CREATE TABLE CreateFlagger
(
	 Id INT
	,Vals VARCHAR(10)
)
GO

INSERT INTO CreateFlagger VALUES
(101,'SQL'),
(102,'SQL'),
(103,'Oracle'),
(104,'Oracle'),
(105,'Oracle'),
(106,'DB2'),
(107,'DB2'),
(108,'mySQL'),
(109,'mySQL'),
(110,'mySQL'),
(111,'mySQL')
GO

SELECT * FROM CreateFlagger
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 | USING FIRST_VALUE Method..

--


SELECT * , CASE WHEN FIRST_VALUE(Id) OVER (PARTITION BY Vals ORDER BY Id DESC) = Id THEN 0 ELSE 1 END Flagger
FROM CreateFlagger
ORDER BY Id


--

Output-1

--                            


Id          Vals       Flagger
----------- ---------- -----------
101         SQL        1
102         SQL        0
103         Oracle     1
104         Oracle     1
105         Oracle     0
106         DB2        1
107         DB2        0
108         mySQL      1
109         mySQL      1
110         mySQL      1
111         mySQL      0

(11 rows affected)




--

SOLUTION – 2 | USING ROW_NUMBER() Method.. | Of Course 2 SELECTs

--


SELECT Id,Vals,CASE WHEN rnk = 1 THEN 0 ELSE 1 END Flagger
FROM
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY Vals ORDER BY Id DESC) rnk
	FROM CreateFlagger
)t ORDER BY Id

--

Output-2

--                            


Id          Vals       Flagger
----------- ---------- -----------
101         SQL        1
102         SQL        0
103         Oracle     1
104         Oracle     1
105         Oracle     0
106         DB2        1
107         DB2        0
108         mySQL      1
109         mySQL      1
110         mySQL      1
111         mySQL      0

(11 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.

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