CODEWITHSUNDEEP
sqlsql-server
w0051977
w0051977

Reputation: 15797

Grouping records - ROW NUMBER and PARTITION BY

Please see the DDL below:

create table #Test (ID int identity not null, name varchar(100), primary key (id))
insert into #Test (name) values ('Ian')
insert into #Test (name) values ('Ian')
insert into #Test (name) values ('Ian')
insert into #Test (name) values ('Mark')
insert into #Test (name) values ('James')
insert into #Test (name) values ('James')
insert into #Test (name) values ('Henry')

I am looking for the output below:

Ian 1
Ian 1
Ian 1
Mark 2
James 3
James 3
Henry 4

All the Ians' have the same number. All the James' have the same number. I have been experimenting with ROW NUMBER and PARTITION BY but I have been unsuccessful so far.

Upvotes: 1

Views: 177

Answers (3)

steenbergh
steenbergh

Reputation: 1761

You can get the desired output with:

with prep as
(
    select name
    , DENSE_RANK() over (order by c) [rank]
    from (
        select distinct name, min(id) c
        from #Test
        group by name
    ) a
)
select T.name
, prep.[rank]
from #Test T
inner join prep on prep.name = T.name

The first (sub)query selects the lowest possible ID per record, the DENSE_RANK then ensures the numbering is seqential, and the final query uses those prepped results against the original #Test table to duplicate the data per row.

You can find out more about the ranking functions here: https://msdn.microsoft.com/en-us/library/ms189798.aspx

Upvotes: 0

Siyual
Siyual

Reputation: 16917

Use DENSE_RANK instead.

Select  Name, DENSE_RANK() Over (Order By Name)
From    #Test

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can do this in a few ways, but row_number() per se is not one of them.

Here is a method:

select t.name, dense_rank() over (order by nameid)
from (select t.*, min(id) over (partition by name) as nameid
      from #test t
     ) t;

This calculates the min id for each name and then uses that for dense_rank().

If you don't care about the particular ordering, you can use dense_rank() on the name:

select name, dense_rank() over (order by name)
from #test t;

Upvotes: 2

Related Questions