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

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

Expected Output

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

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.

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