SENARIO_2

My input table is like…………

Stuno Marks address
1001 1 US
1002 1 US
1003 1 UK
1004 1 UK
1005 2 LANDON
1006 2 LONDON
1007 3 BRAZAIL
1008 3 KENADA

MY OUPUT LIKE……………….

Stuno Marks address
1001 1 US
1003 1 UK
1005 2 LANDON
1007 3 BRAZAIL
1008 3 KENADA

ANS:

IF EXISTS(SELECT *FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='STU_DE')

 BEGIN

   DROP TABLE STU_DE

 END

CREATE TABLE STU_DE(STU_ID INT,MARKS INT,ADDR VARCHAR(50))

INSERT INTO STU_DE VALUES (1001,1,'US'),(1002,1,'US'),(1003,1,'UK')

INSERT INTO STU_DE VALUES (1004,1,'UK'),(1005,2,'LONDON'),(1006,2,'LONDON'),(1007,3,'BRAZAIL'),(1008,3,'KENADA')

SELECT STU_ID,MARKS,ADDR from(

select STU_ID,MARKS,ADDR, row_number() Over( partition by MARKS,ADDR order by STU_ID )R from STU_DE)a WHERE a.R =1 

;WITH CTE AS( SELECT STU_ID,MARKS,ADDR,ROW_NUMBER() OVER (PARTITION BY MARKS,ADDR ORDER BY STU_ID) AS ROW_NO FROM STU_DE)

SELECT STU_ID,MARKS,ADDR FROM CTE WHERE ROW_NO=1