oracle-databasegroup-bygreatest-n-per-groupmin

Grouping query based on one column in Oracle

i have result of query like this (example) enter image description here

And i would like result to be enter image description here

I am using 'group by' based on min(id) but still have duplicates because 'source' is different. Please help Thank you

What i have

(https://i.sstatic.net/Dd5wl8O4.png)](https://i.sstatic.net/Dd5wl8O4.png)

What would like to get

(https://i.sstatic.net/BHLwilyz.png)](https://i.sstatic.net/BHLwilyz.png)

My query is based on min(id)

Solution

  • If I understand correctly, for each LPN you want to get the row with the minimal id. This can be achieved with the row_number window function:

    SELECT lpd, id, source
FROM   (SELECT lpd, id, source, 
               ROW_NUMBER() OVER (PARTITION BY lpd ORDER BY id ASC) AS rn
        FROM   mytable)
WHERE  rn = 1