CODEWITHSUNDEEP
sqlsql-servert-sqlselect
cdrrr
cdrrr

Reputation: 1112

Renumbering rows in SQL Server

I'm kinda new into the SQL Server and I'm having the following question: is there any possibility to renumber the rows in a column?

For ex:

id  date         name
1   2016-01-02   John
2   2016-01-02   Jack
3   2016-01-02   John
4   2016-01-02   John
5   2016-01-03   Jack
6   2016-01-03   Jack
7   2016-01-04   John
8   2016-01-03   Jack
9   2016-01-02   John
10  2016-01-04   Jack

I would like that all "Johns" to start with id 1 and go on (2, 3, 4 etc) and all "Jacks" have the following number when "John" is done (5, 6, 7 etc). Thanks!

Upvotes: 1

Views: 1694

Answers (3)

Richa Kumari
Richa Kumari

Reputation: 36

Instead of renumbering the id column, you can use ROW_NUMBER window function to renumber the rows as per your requirement. for e.g.:

SELECT ROW_NUMBER() OVER(PARTITION BY name ORDER BY date) as rowid,date,name
FROM tablename

Upvotes: -1

Mureinik
Mureinik

Reputation: 311478

The id should just be an internal identifier you use for joins etc - I wouldn't change it. But you could query such a numbering using a window function:

SELECT ROW_NUMBER() OVER (ORDER BY CASE name WHEN 'John' THE 1 ELSE 2 END) AS rn,
       date,
       name
FROM   mytable

Upvotes: 1

user3583912
user3583912

Reputation: 1322

I hope this helps..

declare @t table (id  int ,[date] date,name varchar(20))
insert into @t
        ( id, date, name )
values  (1,'2016-01-02','John')
,(2,'2016-01-02','Jack')
,(3,'2016-01-02','John')
,(4,'2016-01-02','John')
,(5,'2016-01-03','Jack')
,(6,'2016-01-03','Jack')
,(7,'2016-01-04','John')
,(8,'2016-01-03','Jack')
,(9,'2016-01-02','John')
,(10,'2016-01-04','Jack')

select 
    row_number() over(order by name,[date]) as ID,
    date ,
    name 
from 
    @t
order by name

Upvotes: 1

Related Questions