I have the following table redshift.
| guest_id | name | rownum |
|---|---|---|
| 1 | Safvan | 1 |
| 1 | Safvan | 2 |
| 1 | Thomas | 3 |
| 2 | Anandu | 1 |
| 2 | Manish | 2 |
I need to delete all the records in each partition based on guest_id except the record having max(rownum).
The result should be like
| guest_id | name | rownum |
|---|---|---|
| 1 | Thomas | 3 |
| 2 | Manish | 2 |
Thanks in advance for valuable helps.
My solution is :
create table table_rownum as (
select
*,
row_number() over (partition by guest_id
order by
rownum desc) as rownum_temp
from
table_orig);
delete from table_rownum where rownum_temp<>1;
alter table table_rownum drop column rownum_temp;
truncate table table_orig;
insert into table_orig (select * from table_rownum);
drop table table_rownum;
Please suggest if there is better solution.
Subquery returns guest_id wise max row then JOIN with main table where matching guest_id and max_row not equal row_num then perform DELETE.
DELETE redshift
FROM redshift r
INNER JOIN (SELECT guest_id
, MAX(rownum) rownum
FROM redshift
GROUP BY guest_id) t
ON r.guest_id = t.guest_id
AND r.rownum != t.rownum
Please check from https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=47081e3517000949460932808ac9f09d
Delete duplicate records by using CTE
WITH t_cte AS (
SELECT *
, ROW_NUMBER() OVER (PARTITION BY guest_id ORDER BY rownum DESC) row_num
FROM redshift
)
DELETE redshift
FROM t_cte c
INNER JOIN redshift r
ON c.guest_id = r.guest_id
AND c.row_num > 1 AND c.rownum = r.rownum
Please check from url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=90b7099ca779c0836b90278ae1b3635a
WITH cte AS ( SELECT * , ROW_NUMBER() OVER (PARTITION BY guest_id ORDER BY rownum DESC) row_num FROM redshift ) DELETE FROM redshift rs inner join cte on rs.rownum=cte.row_num WHERE cte.row_num<>1; This is the correct query?Delete from table name where rownum not in (select max(rownum) from table name groupby column)
