Delete certain rows that match if a value in another column exists

Question

I have a table with rows that look like this

+---------------+-------------+------+-------+ | ID | Name | EL | STRM | +---------------+-------------+------+-------+ | 1 | Tilley | 1 | 1150 | | 1 | Tilley | 2 | 1170 | | 1 | Tilley | 1 | 1190 | | 2 | Lin | 1 | 1170 | | 2 | Lin | 1 | 1190 | | 3 | Jake | 2 | 1170 | | 4 | Sue | 1 | 1150 | | 4 | Sue | 1 | 1190 | | 5 | Jill | 2 | 1150 | +---------------+-------------+------+-------+

I need to make it so that 1 of every unique ID is in the table, but I need it to be in an order that is not ASC, or DESC.

For instance my main value is 1170, if that value exists then delete all other values and keep that one, But if it does not exist then then next important value is 1190 and finally 1150. In the end the table should look something like this:

+---------------+-------------+------+-------+ | ID | Name | EL | STRM | +---------------+-------------+------+-------+ | 1 | Tilley | 2 | 1170 | | 2 | Lin | 1 | 1170 | | 3 | Jake | 2 | 1170 | | 4 | Sue | 1 | 1190 | | 5 | Jill | 2 | 1150 | +---------------+-------------+------+-------+

I tried order by's but I can't seem to get a SQL statement that can do this, and I want to avoid a cursor since its a rather big file. Any ideas?

Gordon Linoff · Accepted Answer · 2020-07-15 15:56:00Z

You can use row_number():

select t.* from (select t.*, row_number() over (partition by id order by case when strm = 1170 then 1 when strm = 1190 then 2 when strm = 1150 then 3 else 4 end ) as seqnum from t ) t where seqnum = 1;

this was perfect I had to add a ) to make it work but this was the solve!

vmedhe2 · Accepted Answer · 2020-07-15 15:42:07Z

Thanks Gordon Linoff, that was the answer!

for future googlers I just had to add my table name and END to get it to work:

select t.* from (select t.*,row_number() over (partition by [Student ID] order by case when strm = 1170 then 1 when strm = 1190 then 2 when strm = 1150 then 3 else 4 END) as seqnum from #tempfinal t ) t where seqnum = 1;

Collectives™ on Stack Overflow

Delete certain rows that match if a value in another column exists

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related