Reputation: 167
I have the following result set of "Baseline Name", "Version"
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:
Does anyone have tips on how to do this?
Thank you, Matt
Upvotes: 0
Views: 76
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
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