Tags

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


T-SQL Query | [ Ignore duplicate values while selecting data ]  – In this puzzle we have to ignore duplicate values while selecting data. Please check out the sample input and expected output for details.

Sample Input

a b
1 1
1 2
1 3
1 1

Expected Output

a b
1 1
1 2
1 3

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

Script

Use the below script to generate the source table and fill them up with the sample data.


--Create Table

CREATE TABLE Test2DistinctCount
(
a Int
,b Int
)
Go

--Insert Data

INSERT INTO Test2DistinctCount VALUES 
(1,1) , (1,2) , (1,3) , (1,1)

--Verify Data

SELECT a,b FROM Test2DistinctCount

Update May 14 | Solution


--

/************   SOLUTION 1    | Pawan Kumar Khowal     ****************/

SELECT DISTINCT a,b FROM Test2DistinctCount 


/************   SOLUTION 2    | Pawan Kumar Khowal     ****************/

SELECT a,b FROM Test2DistinctCount GROUP BY a,b


/************   SOLUTION 3    | Pawan Kumar Khowal     ****************/
;WITH CTE AS
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY a,b ORDER BY %%Physloc%%) rnk FROM Test2DistinctCount
)
SELECT a,b FROM CTE WHERE rnk = 1



--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com