Select record order by Birthday and assign Row Number in SQL Server

Hi PRA,

Your query is right. As per your query first it order by Id and then by Birthday.

If you want to order by Birthday first then you have to pass the Birthday first in order by clause.

Hi! Can I order by Birthday and Id or it's not possible? Because by using top query I can't order by birthday and Id. For me need order by birthday and Id.

Hi PRA,

Refer below query.

SQL

DECLARE @InfoTable AS TABLE(Id INT IDENTITY(1,1),LastName VARCHAR(25),Name VARCHAR(25),Birthday date,Address VARCHAR(50))
     
INSERT INTO @InfoTable VALUES('Pulodov','Rustam','1987-12-22','city Dushanbe')
INSERT INTO @InfoTable VALUES('Soliev','Firuzjon','1988-06-16','city Hisor')
INSERT INTO @InfoTable VALUES('Sharipov','Sadriddin','1991-12-08','city Dushanbe')
INSERT INTO @InfoTable VALUES('Yakubi','Hasanzod','1991-05-15','city Dushanbe')
INSERT INTO @InfoTable VALUES('Sayfurov','Karomatullo','1980-05-07','city Dushanbe')
INSERT INTO @InfoTable VALUES('Asrori','Yatim','1978-02-06','city Dushanbe')
 
select row_number() over(order by Id) AS Id, LastName, Name, Birthday, CONVERT(VARCHAR,CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25))Age, Address 
from @InfoTable 
order by Birthday, Id

Output

SQL

select LastName, Name, Birthday, CONVERT(VARCHAR,CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25)) Age, Address 
INTO #Temp
from @InfoTable order by Birthday
SELECT row_number() over(order by (SELECT 0)) AS Id,* FROM #Temp
DROP TABLE #Temp

Output

My requirment is after order by birthday must Id be by order like below:

Id LastName Name Birthday Age Address 1 Asrori Yatim 1978-02-06 39 city Dushanbe 2 Sayfurov Karomatullo 1980-05-07 37 city Dushanbe 3 Pulodov Rustam 1987-12-22 29 city Dushanbe 4 Soliev Firuzjon 1988-06-16 29 city Hisor 5 Yakubi Hasanzod 1991-05-15 26 city Dushanbe 6 Sharipov Sadriddin 1991-12-08 25 city Dushanbe

Hi PRA,

I have modified my previous reply by adding the modified query.

http://www.aspforums.net/Threads/243798/Order-by-newId-and-Age-in-Sql/Replies/3#Replies