CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlgreatest-n-per-group
Surensiveaya
Surensiveaya

Reputation: 347

Avoiding Repetition from one column

I have a table 

sid id  rNum    str      power  con1  rc
15  287 6       Grit     0      0    225
15  288 7       Short    0      0    225
15  289 8       Grade    0      0    225
16  224 3       Mega     0      1    222
17  432 7       Server   0      0    656

I wish to have result as

sid id  rNum    str      power  con1  rc
15  287 6       Grit     0      0    225
16  224 3       Mega     0      1    222
17  432 7       Server   0      0    656

Which means I want to show unique "sid" with the very first value from the group

I have tried the distinct but not getting desired result.

Upvotes: 0

Views: 64

Answers (2)

forpas
forpas

Reputation: 164064

With row_number() window function:

select t.sid, t.id, t.rNum, t.str, t.power, t.con1, t.rc
from (
  select *, row_number() over (partition by sid order by id) rn
  from tablename
) t
where t.rn = 1

Upvotes: 1

GMB
GMB

Reputation: 222402

You can filter with a subquery. Assuming the the first record per sid is the record with the smallest id, that would be:

select t.*
from myable t
where t.id= (select min(r1.id) from mytable t1 where t1.sid = t.sid)

Alternatively, you can also use row_number():

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

Upvotes: 2

Related Questions