I have a table like below:
ID NAME SCHOOL GRADUATIONYEAR DEGREE MAJOR
--------------------------------------------------------------------------------------------------
100 Ben University1 2015 Bachelor of Arts Major1
100 Ben University1 2015 Bachelor of Arts Major2
100 Ben University2 2017 Master of Science Major3
Since Ben has two records that are from the same university due to double majors (as you can see same School, graduation year, and degree), I only want to pull one of those records rather than both of those records, it doesn't matter which Major I pull, just has to be one instead of both of those records. So ideally, I'd like for it to be like below:
ID NAME SCHOOL GRADUATIONYEAR DEGREE MAJOR
--------------------------------------------------------------------------------------------------
100 Ben University1 2015 Bachelor of Arts Major1
100 Ben University2 2017 Master of Science Major3
I have tried to do a delete like below, but this ends up deleting all of the records, is it possible to maybe use CASE in this scenario?
DELETE FROM #table1
WHERE ID = ID AND NAME = NAME AND SCHOOL = SCHOOL AND DEGREE = DEGREE
Use row_number():
select *
from (
select
t.*,
row_number() over(
partition by name, school, graduationyear, degree
order by major
) rn
from mytable t
) t
where rn = 1
When two rows have the same ame, school, graduation year and degree, the query retains the one with the smallest major, alphabetically-wise (you can change the order by clause of row_number() if you want another criteria).
If you wanted a delete statement:
with cte as (
select row_number() over(
partition by name, school, graduationyear, degree
order by major
) rn
from mytable
)
delete from cte where rn > 1