February 23, 2012 at 4:27 am
-- I Have a table
create table #temp
(
Name varchar(10)
,ID int NULL
)
INSERT INTO #temp
select 'ashok'
UNOIN
select 'raju'
UNOIN
select 'ABC'
UNOIN
select 'XYZ'
-- NOW i want to get the Output
#temp
ID NAME
1 ABC
2 ashok
3 raju
4 XYZ
-- How to achieve this using ROW_NUMBER()
February 23, 2012 at 4:38 am
Did you try this?
SELECT ROW_NUMBER() OVER (ORDER BY [Name]) AS RowId,[Name]
FROM #Temp
ORDER BY [Name]
February 23, 2012 at 4:40 am
This is logically correct but the data in the table is not updated with this select statement.
I Have to update the data on ID column.
February 23, 2012 at 4:43 am
Why not to use IDENTITY?
The following code will do it, but will only work properly if your Names are unique:
UPDATE t SET Id = n.Id
FROM #temp t
JOIN (SELECT Name, ROW_NUMBER() OVER (ORDER BY Name) Id
FROM #temp
) n
ON t.Name = n.Name
If names are not unique, your best try would be:
SELECT Name, IDENTITY(int,1,1) Id
INTO #temp_id
FROM #temp
ORDER BY Name
DELETE #temp
INSERT #temp SELECT * FROM #temp_id
February 23, 2012 at 4:46 am
with cte1 as
(
SELECT ROW_NUMBER() OVER (ORDER BY [Name]) AS RowId,[Name]
FROM #Temp
)
update #temp
set ID = rowid
from
#temp t1
inner join
cte1 c
on t1.Name = c.name
February 23, 2012 at 4:50 am
Divine Flame (2/23/2012)
Did you try this?
SELECT ROW_NUMBER() OVER (ORDER BY [Name]) AS RowId,[Name]
FROM #Temp
ORDER BY [Name]
If you just need to reset the ID column (with names in order) so you can simply add an IDENTITY column in your table with seed 1 & query that column.
ALTER TABLE #Temp ADD NewIdCol INT IDENTITY(1,1)
February 23, 2012 at 4:56 am
Divine Flame (2/23/2012)
Divine Flame (2/23/2012)
Did you try this?
If you just need to reset the ID column (with names in order) so you can simply add an IDENTITY column in your table with seed 1 & query that column.
ALTER TABLE #Temp ADD NewIdCol INT IDENTITY(1,1)
He could, but it will not guarantee ID allocation in the order by name...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply