CODEWITHSUNDEEP
postgresql
Sneha Nadar
Sneha Nadar

Reputation: 65

Query to remove duplicate

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

Upvotes: 0

Views: 81

Answers (3)

Anton Grig
Anton Grig

Reputation: 1719

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

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 247215

With PostgreSQL, you can simply use DISTINCT ON:

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

Upvotes: 0

Kurt Kline
Kurt Kline

Reputation: 2069

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;

Upvotes: 0

Related Questions