CODEWITHSUNDEEP
sqloracle-database
Egg_Egg
Egg_Egg

Reputation: 177

Select one row of duplicated row with multiple columns as distinct

Is there anyway in Oracle, it can select the first row of the rows with duplicated value in column val1 and val2?

ID  val1    val2
==================
1   1       1
2   1       1
3   2       2
4   2       3
5   2       3
6   3       2
7   3       2
8   2       2

Expect:
ID  val1    val2
==================
1   1       1
3   2       2
4   2       3
6   3       2
8   2       2

Upvotes: 0

Views: 385

Answers (3)

nilsman
nilsman

Reputation: 396

SELECT DISTINCT first_value(ID) over (PARTITION BY val1, val2 ORDER BY ID), val1, val2 FROM your_tbl

But it shurely will not give you row (8, 2, 2), because val1 and val2 are the same as in row (3, 2, 2).

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

You can use a pair of subselect 

select ID, val1, val2 
from my_table 
where (id) in (select min(id) from my_table where (vali1, val2) in 
(select distinct val1,val2 from my_tbale))

Upvotes: 0

dotnetom
dotnetom

Reputation: 24901

You can use ROW_NUMBER to help you:

SELECT t.ID, t.val1, t.val2 FROM
    (SELECT ID, val1, val2, ROW_NUMBER()
       OVER (PARTITION BY val1, val2 ORDER BY ID) AS rownumber
       FROM table1) t
WHERE t.rownumber = 1

Here I use a subquery, where a get a row number for each record, partitioning the records by val1 and val2. Then I use outer query to filter only row that have rownumber=1, which returns the values that you need.

UPDATE: I did not notice, that you can have the same repeating values in your expected result (e.g. IDs 3 and 8 should both be displayed in results), where my initial query would not include ID 8.

Here is alternative solution that gives results just like in your expected results:

SELECT 
    t.ID, 
    t.val1, 
    t.val2 
FROM (
    SELECT 
        ID, 
        val1, 
        val2, 
        LAG(val1) OVER (ORDER BY ID) as previousVal1, 
        LAG(val2) OVER (ORDER BY ID) as previousVal2
    FROM table1) t
WHERE 
    (t.previousVal1 IS NULL and t.previousVal2 IS NULL) OR 
    t.val1 <> t.previousVal1 OR 
    t.val2 <> t.previousVal2
ORDER BY t.ID

Here in subquery I used function LAG to get previous values of val1 and val2. Then in outer query I select a first record (specified by condition t.previousVal1 IS NULL and t.previousVal2 IS NULL) and all records where values of val1 and val2 are different from the previous record.

Upvotes: 2

Related Questions