Query to remove duplicate

Question

I need to select distinct rows like the below example

Source table values

Column 1 | Column 2

A  A10
A  A11
A  A12
B  B12
c  C11
D  D10
D  D78

Expected output after select query:

Column 1 | Column 2

A  A10
B  B12
C  C11
D  D10

So if there are duplicates in column 1 I need to get distinct rows by selecting records in col2 that ends with 10.....if col1 is unique go ahead with the col2 value.....it need not be ending with 10

Update: cannot apply distinct on as there are other columns as well with different values for each row and cannot use temp table as I need to accommodate it in a where clause of single select query

Use [distinct on](https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by) — Stefanov.sm, Sep 26 '21 at 20:36

Anton Grig · Accepted Answer · 2021-09-27T13:22:18.903

You can query distinct rows using union. The upper part of the union query all rows ending with 10. The lower part of the union query rows with unique value in the first column. The same rows from the upper and lower part of the union will be handled by union itself.

Select Col1, Col2
From (
Select Col1, Col2
From Tbl
Where Col2 Like '%10'
Union
Select Col1, Max(Col2)
From Tbl
Group by Col1
Having Count(*)=1) As T
Order by Col1

with single select query

Select T.Col1, Max(T.Col2)
From Tbl As T Left Join Tbl As T1 On (T.Col1=T1.Col1 And T.Col2<>T1.Col2)
Where T1.ID Is Null Or
     (T1.ID Is Not Null And T.Col2 Like '%10')
Group by T.Col1
Order by T.Col1

score 0 · Answer 2 · answered Sep 26 '21 at 21:55

temp1 - find col2's with 10 as the right 2 characters
temp2 - create row numbers for each col1 group, sorting by has_10 desc
final select - get all rownum = 1

with temp1 as (
    select col1, 
           col2, 
           case when RIGHT(col2, 2) = 10 then 1
                else 0
           end as has_10
    from source_table 
),
temp2 as (
    select col1, 
           col2, 
           row_number() over(
               partition by col1
               order by has_10 desc
           ) as rownum
    from temp1
)
select col1, 
       col2
from temp2 
where rownum = 1;

score 0 · Answer 3 · answered Sep 27 '21 at 06:03

0

With PostgreSQL, you can simply use DISTINCT ON:

SELECT DISTINCT ON (col1) col1, col2
FROM tab
ORDER BY col2;

answered Sep 27 '21 at 06:03

Laurenz Albe

209,280
17
206
263

There are other columns as well that depends upon this two values.....so cannot apply distinct on...sorry for not mentioning it there will update it – Sneha Nadar Sep 27 '21 at 09:13

Query to remove duplicate

3 Answers3