CODEWITHSUNDEEP
sqloracle-databasegroup-bygreatest-n-per-group
Matthew Holt
Matthew Holt

Reputation: 167

Oracle/SQL - Removing duplicate entries based on order?

I have the following result set of "Baseline Name", "Version"

enter image description here

Each Baseline entry has repeated rows from baselines below it in the result set. I want to only keep the first instance of the "Version" being seen (starting at Baseline 1 and working up), so that the result looks like:

enter image description here

Does anyone have tips on how to do this?

Thank you, Matt

Upvotes: 0

Views: 76

Answers (2)

GMB
GMB

Reputation: 222722

Assuming that you have an ordering column (say, id), you can use row_number():

select *
from (
    select t.*, row_number() over(partition by version order by id) rn
    from mytable t
) t
where rn = 1

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

You want the minimum baseline per version:

select min(baseline), version
from mytable
group by version
order by baseline desc, version asc;

(This works as long as baselines and versions can be ordered. If you have baseline strings like 'baseline 1', 'baseline 2', ... 'baseline 10', then this doesn't work for instance, as 'baseline 10' comes between 'baseline 1' and 'baseline 2' in alphabetical order.)

Upvotes: 1

Related Questions