Removing duplicates and retaining unique values in a table

Question

I have a table with the following data. How do I retrieve unique values from the table. My code gives me unique values but removes the statuschangeid 7 which does not have duplicates and find the latest approvalpending for that dupid. The valid row for a particular dupid is the max of statuschangeid for that group. What am I doing wrong here?

Actual Table

statuschangeid   dupid  approvalpending 
1                  1           1
2                  1           1
3                  1           0
4                  2           0
5                  2           0
6                  2           0
7                  3           0

Expected Table

statuschangeid   dupid  approvalpending 
3                  1           0
6                  2           0
7                  3           0

Output I'm getting

statuschangeid   dupid  approvalpending 
3                  1           0
6                  2           0

The code im using is :

select dupid, approvalpending, max(statuschangeid) as maxid from tableA group by dupid, approvalpending having count(*) > 1

Answer 1

If you don't want to delete the rows but only a select query, use NOT EXISTS:

select a.statuschangeid, a.dupid, a.approvalpending
from tableA a
where not exists (
  select 1 from tableA
  where dupid = a.dupid and statuschangeid > a.statuschangeid
)

See the demo.
Results:

> statuschangeid | dupid | approvalpending
> -------------: | ----: | --------------:
>              3 |     1 |               0
>              6 |     2 |               0
>              7 |     3 |               0