SELECT column1, column2, column3 from( SELECT column1, column2, column3, row_number() over ( partition BY column1, column2 ORDER BY CASE WHEN column3 = 'N/A' THEN 999999999 ELSE to_number(column3) END ) rn FROM table1) WHERE rn = 1
if OBJECT_ID(N'tempdb..#mytable) IS NOT NULL BEGIN DROP TABLE END
create table #mytable( PN varchar(10) GN int LN int NM varchar (5) PR int )
INSERT INTO #MYTABLE VALUES ('ABC123', 0, 1, 'GA' 10) INSERT INTO #MYTABLE VALUES ('ABC123', 1, 1, 'TX' 10) INSERT INTO #MYTABLE VALUES ('ABC123', 2, 1, 'GA' 10) INSERT INTO #MYTABLE VALUES ('ABC123', 3, 2, 'CA' 10) INSERT INTO #MYTABLE VALUES ('ABC123', 4, 1, 'GA' 10) INSERT INTO #MYTABLE VALUES ('ABC123', 4, 2, 'NY' 10) INSERT INTO #MYTABLE VALUES ('ABC123', 5, 1, 'GA' 10)
Row PN GN LN NM PR
1 ABC123 0 1 GA 10 2 ABC123 1 1 TX 10 3 ABC123 2 1 GA 10 4 ABC123 3 2 CA 10 5 ABC123 4 1 GA 10 6 ABC123 4 2 NY 10 7 ABC123 5 1 GA 10
with cte as (select , ROW_NUMBER() OVER (PARTITION BY MT.PN,MT.LN,MT.NM,MT.PR ORDER BY MT.PN,MT.GN,MT.LN) RN FROM (SELECT * FROM #MYTABLE) MT) SELECT CTE. FROM CTE WHERE RN=1 ORDER BY PN,GN,LN
O/P Row PN GN LN NM PR
1 ABC123 0 1 GA 10 2 ABC123 1 1 TX 10 4 ABC123 3 2 CA 10 6 ABC123 4 2 NY 10
EXPECTED: Row PN GN LN NM PR
1 ABC123 0 1 GA 10 2 ABC123 1 1 TX 10 3 ABC123 2 1 GA 10 4 ABC123 3 2 CA 10 6 ABC123 4 2 NY 10
Expected row 3 is missing because of not using column GN. Requirement is , I need to populate only the rows based on GN. if latest GN row is already having duplicate LN, NM, PR, I need to populate only the first occurrence of LN,NM,PR combination.