How to remove duplicate rows based on condition without creating temp table

Question

Can someone help me to get a result query. I've been stuck here in a couple of hours. Sorry Im just new to SQL. I need to get the latest date(column3) without duplicates values from Column1 and Column2

SELECT COLUMN1, COLUMN2, COLUMN3
FROM TABLE1 t1
LEFT JOIN
     TABLE2 t2
 ON t1.column1 = t2.column1

Table Value:

COLUMN1        COLUMN2        COLUMN3
VAL1             1234        2019-05-12    
VAL1             1234        2019-04-12
VAL1             1234        2019-12-12
VAL2             4321        2019-11-12
VAL2             3333        2019-12-12
VAL3             2222        2019-10-10

Expected Result:

COLUMN1         COLUMN2        COLUMN3
VAL1             1234        2019-12-12
VAL2             3333        2019-12-12
VAL3             2222        2019-10-10

Won't work for `'Val2'` @HoneyBadger ; as you would either need to Group on `COLUMN2` resulting in 2 rows, or apply `MAX` to `Val2`, which would return `4321` not `3333`. This needs the common `ROW_NUMBER` solution. — Thom A, Nov 04 '19 at 12:05
Hi I've used MAX() and DISTINCT, and GROUP BY but I cant get the output I've expecting. Any idea? — Arvin John Salandanan, Nov 04 '19 at 12:09
There are 3 duplicates for you @ArvinJohnSalandanan . All of those show you how to use a CTE/subquery and `ROW_NUMBER` to achieve this. — Thom A, Nov 04 '19 at 12:09
So I really need to use temp table in order to achieve this? — Arvin John Salandanan, Nov 04 '19 at 12:10
*"So I really need to use temp table in order to achieve this?"* No, @ArvinJohnSalandanan , those solutions use a subquery or CTE. None of them use a Temporary Table. If you are using a solution with a Temporary Table, you can't using any of the linked duplicates answers, or accepted solutions (as none of them use one). — Thom A, Nov 04 '19 at 12:15

score 1 · Accepted Answer · answered Nov 04 '19 at 12:38

SQL Fiddle

MS SQL Server 2017 Schema Setup:

create table MyTable (COL1 varchar(max), COL2 int,COL3 date)
insert into MyTable (COL1, COL2,COL3 )VALUES('VAL1',1234,'2019-05-12')
insert into MyTable (COL1, COL2,COL3 )VALUES('VAL1',1234,'2019-04-12')
insert into MyTable (COL1, COL2,COL3 )VALUES('VAL1',1234,'2019-12-12')
insert into MyTable (COL1, COL2,COL3 )VALUES('VAL2',4321,'2019-12-12')
insert into MyTable (COL1, COL2,COL3 )VALUES('VAL2',3333,'2019-12-12')
insert into MyTable (COL1, COL2,COL3 )VALUES('VAL3',2222,'2019-10-10')

Query 1:

select t.COL1,t.COL2,t.COL3
from (select m.*,
             row_number() over (partition by m.COL1
                                order by m.COL2,m.COL3 desc) as seqnum
      from MyTable m
     ) t
where t.seqnum=1

Results:

| COL1 | COL2 |       COL3 |
|------|------|------------|
| VAL1 | 1234 | 2019-12-12 |
| VAL2 | 3333 | 2019-12-12 |
| VAL3 | 2222 | 2019-10-10 |

How to remove duplicate rows based on condition without creating temp table

1 Answers1