Row Number - 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

SQL Server 2012 Forums

Transact-SQL (2012)

Row Number

Author

Topic

wided
Posting Yak Master

218 Posts

Posted - 2014-04-11 : 07:05:01

I have a table that has data:

I want to number the lines like this:

Code NumLine Account Amount

AB-II_140008 1 423101 442.854
AB-II_140008 2 532111 0.000
AB-II_140009 1 401102 50.000
AB-II_140009 2 532111 0.0000
AB-II_140009 3 532556 12.000

I user this query

(SELECT ROW_NUMBER() OVER(PARTITION BY code ORDER BY code ASC))
but i always 1 on all lines

I have to renumber from 1 to each code change

thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-04-11 : 07:17:11

Can't see anything wrong in the given snippet...

Please show the complete statement.

Too old to Rock'n'Roll too young to die.

wided
Posting Yak Master

218 Posts

Posted - 2014-04-11 : 07:38:20

I want reincrement from 1 to each code change
but my query don't work

wided
Posting Yak Master

218 Posts

Posted - 2014-04-11 : 07:42:04

I have this

AB-II_140008 1 423101 442.854
AB-II_140008 1 532111 0.000
AB-II_140009 1 401102 50.000
AB-II_140009 1 532111 0.0000
AB-II_140009 1 532556 12.000

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-11 : 08:07:06

[code]
;with aCTE
AS
(
select 'AB-II_140008' as Code union all
select 'AB-II_140008' union all
select 'AB-II_140008')

select
*
,ROW_NUMBER() OVER(PARTITION BY replace(Code,' ','') ORDER BY code ASC) as rn
from aCTE
[/code]

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-11 : 08:08:40

you use
(SELECT ROW_NUMBER() OVER(PARTITION BY code ORDER BY code ASC))

so, this always return 1 , because it's SELECT.

use this:

 ROW_NUMBER() OVER(PARTITION BY code ORDER BY code ASC)

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-11 : 08:10:44

[code]
select
*
,(SELECT ROW_NUMBER() OVER(PARTITION BY replace(Code,' ','') ORDER BY code ASC)) as rn
from aCTE
[/code]

You spot the difference, it's a query inside the query

sabinWeb MCP

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-04-11 : 08:21:00

You do not need a subquery


with aCTE
AS 
	(
	select 'AB-II_140008' as Code union all
	 select 'AB-II_140008' union all
	 select 'AB-II_140008')



SELECT code,ROW_NUMBER() OVER(PARTITION BY code ORDER BY code ASC) from acte

Madhivanan

Failing to plan is Planning to fail

wided
Posting Yak Master

218 Posts

Posted - 2014-04-11 : 08:29:11

Thanks Stepson It's OK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources