stack stack - 4 months ago 13
SQL Question

Serial NO starting from 1

I have a query in which, I want one columns as

SR_No
which should start from 1

Below is my query

SELECT
top 2
userName,
Party_name,
Ref_No,
Ref_date,
doc_date,
Last_Action_date,
RAName,
COUNT(Doc_No) AS CountofDocNo,
Document_Type,
RA1_Email
FROM #MainTempTable
GROUP BY RAName,
userName,
Document_Type,
RA1_Email ,Party_name,Ref_No,
Ref_date, doc_date,Last_Action_date
order by RAName


How to start it's serial no from 1.

I tried with
Row_NUM()
but it was not starting from 1.

UPDATE

I tried Madhivnan's solution but it didn't worked for my second query

SELECT
top 2
row_number() over (order by (select 0)) as SR_No,
UserName,
RAName,
Party_Name,
Ref_No,
Ref_date,
doc_date,
Last_Action_date,
Document_Type,
Doc_No,
No_Of_Days_Doc_Pending,
UserEmail,RA1_Email
FROM #MainTempTable order by UserName

Answer

Try this

SELECT         
top 2 row_number() over (order by (select 0)) as SR_No,     
 userName,   
 Party_name, 
 Ref_No,
 Ref_date,   
 doc_date,
 Last_Action_date,
 RAName,          
 COUNT(Doc_No) AS CountofDocNo,                  
 Document_Type,           
 RA1_Email                  
FROM #MainTempTable         
 GROUP BY RAName,          
 userName,           
 Document_Type,          
 RA1_Email ,Party_name,Ref_No,
 Ref_date, doc_date,Last_Action_date    
order by RAName