ganders ganders - 1 year ago 39
SQL Question

Getting row number from my results

My brain is not working tonight!

Data



Id int, Name varchar, Tag varchar, MilesAway decimal(9,6)


I'm trying to add the
row_number
to my result set, but I want to ignore the duplicate rows that are caused by the tag. Here's expected output:


  • 1 Blah Red 6.3 -> desired output: 1

  • 1 Blah Stripe 6.3 -> desired output: 1

  • 19 SomethingElse Red 12.1 -> desired output: 2

  • 19 SomethingElse Straight 12.1 -> desired output: 2

  • 5 Hello Red 15.9 -> desired output: 3

  • 42 Bye Red 55.0 -> desired output: 4



So, I should have result numbers 1, 2, 3, 4. Here's how I'm writing my query:

select Id,
Name,
Tag,
MilesAway,
row_number() over (order by MilesAway)
from table t
group by Id,
Name,
Tag,
MilesAway


I've also tried adding
partition by
to all columns except tag. I know this, I've written hundreds of these, why am I stumped (other than it being midnight!)?

Edit: I want to ignore the fact that there are 2 records in spot number 1, and still want the next one to be 2, NOT 3.

Answer Source

You are looking for dense_rank:

select Id,
    name,
    Tag,
    MilesAway,
    dense_rank() over (
        order by MilesAway
        )
from table t;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download