Auto increment column

Hi Experts
i need to add an auto increment column to and distinct SQL query that was generated from #Temp table
--------------------
select distinct b.* from #Temp1 as a
inner join #TempCourses as b on a.CourseId = b.Courseid and a.[Request Date] = b.[From Date]
-------------------
LVL 1
AZZA-KHAMEESAsked:
Who is Participating?
 
Dorababu MConnect With a Mentor Senior Software EngineerCommented:
You can use ROW_NUMBER some thing like this
SELECT row_number() OVER (ORDER BY col1) n,
       col1
  FROM table1 

Open in new window


select row_number() OVER (ORDER BY b.col) n,distinct b.* from #Temp1 as a 
inner join #TempCourses as b on a.CourseId = b.Courseid and a.[Request Date] = b.[From Date]

Open in new window

1
 
Máté FarkasDatabase Developer and AdministratorCommented:
It would be better to add a new column to that temporary table when you create it but it is also good to add it later with identity default:

ALTER TABLE #Temp1
        ADD NewID INT NULL IDENTITY(1,1)

SELECT * FROM #Temp1

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.