October 13, 2010 at 8:38 am
Ok, so I wrote this query:
SELECT 'HANSEN8', '2010-07-01 00:00:00.000', A.ADDRKEY, ' ', ROW_NUMBER() OVER( ORDER BY B.ROUTEKEY, SUBSTRING(A.ACCOUNTNUMBER, 4, 5)
), 'N', '1', '1', B.ROUTEKEY,
SUBSTRING(A.ACCOUNTNUMBER, 4, 5)
FROM BILLING.ACCOUNT A
INNER JOIN METERMANAGEMENT_WATER.ROUTE B
ON LEFT(A.ACCOUNTNUMBER, 3) = B.ROUTEID
WHERE A.ADDRKEY > '1' AND A.ACCOUNTSTATUS = 'A'
The query works perfectly and gives me the data that I want, except one thing. The Row_number is just counting all the rows, which makes sense; however, I want it to count the rows only where the routekey is the same. For example, if the routekey is 1000, then count 1, 2, 3, ..... until the routekey changes to 1001, then start over with 1, 2, 3, ....
Is this possible?
Thanks in advance!
Jordon
October 13, 2010 at 8:43 am
Use partition by:
SELECT 'HANSEN8',
'2010-07-01 00:00:00.000',
A.ADDRKEY,
' ',
ROW_NUMBER() OVER (PARTITION BY ROUTEKEY ORDER BY B.ROUTEKEY, SUBSTRING(A.ACCOUNTNUMBER, 4,5)),
'N',
'1',
'1',
B.ROUTEKEY,
SUBSTRING(A.ACCOUNTNUMBER, 4, 5)
FROM BILLING.ACCOUNT A
INNER JOIN METERMANAGEMENT_WATER.ROUTE B
ON LEFT(A.ACCOUNTNUMBER, 3) = B.ROUTEID
WHERE A.ADDRKEY > '1'
AND A.ACCOUNTSTATUS = 'A'
-- Gianluca Sartori
October 13, 2010 at 8:45 am
That worked perfectly! Thank you very much!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply