Need Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need Query

Author

Topic

yaman
Posting Yak Master

213 Posts

Posted - 2012-05-04 : 08:48:00

Sir ,

I have one table

======================
name age count
======================
mukesh 23 NULL
mukesh 23 NULL
mukesh 23 NULL
mukesh 23 NULL
yaman 21 NULL
yaman 21 NULL
yaman 21 NULL

I need result like

======================
name age count
======================
mukesh 23 0
mukesh 23 1
mukesh 23 2
mukesh 23 3
yaman 21 0
yaman 21 1
yaman 21 2
==========================

Please help me out Sir

Yaman

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-05-04 : 09:05:39

select name,Age, ROW_NUMBER() over(partition by name order by age) -1
from yourtable

Jim

Everyday I learn something that somebody else already knew

yaman
Posting Yak Master

213 Posts

Posted - 2012-05-04 : 10:24:19

quote:
Originally posted by jimf

select name,Age, ROW_NUMBER() over(partition by name order by age) -1
from yourtable

Jim

Everyday I learn something that somebody else already knew

Thanks Sir,

I I need to update table rows in one column then how can I update rows with that values in one query. Please help

Yaman

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-04 : 10:38:21

You can put Jim's query into a subquery or cte and then do the update. First run this query to make sure that you are getting the results you want.

;with cte as
(
	select name,Age, [count],ROW_NUMBER() over(partition by name order by age) -1 AS newCount
	from yourtable
)
SELECT * FROM cte;

If the newCount column looks like it has the correct values, then run this query to do the update:

;with cte as
(
	select name,Age, [count],ROW_NUMBER() over(partition by name order by age) -1 AS newCount
	from yourtable
)
UPDATE cte SET [count] = newCount;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources