Query to remove duplicate-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

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 ''
Union
Select Col1, Max(Col2)
From Tbl
Group by Col1
Having Count(*)=1) As T
Order by Col1

CodePudding user response：

With PostgreSQL, you can simply use DISTINCT ON:

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