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