Simple GROUP query

marper
marper used Ask the Experts™
on
I need help in creating a simple query which can be solved with GROUP clause I believe.
Here is example:

TABLE A has the following records:
CUSTOMER_NAME   CUSTOMER_AMOUNT
----------------------------------------------------
CUST_A  150
CUST_A 200
CUST_A 100
CUST_B 200
CUST_B 100
CUST_C 100

As you can see, CUST_A has 3 entries, CUST_B 2 and CUST_C only one
I need to create a sequence for each customer, so  I need a query that will return the following values:
CUST_A 1
CUST_A 2
CUST_A 3
CUST_B 1
CUST_B 2
CUST_C 1

Please advice
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Hello marper,

Without an identity column how do you expect the ordering for each customer to work?

Regards,

Patrick
select customer_name, row_number() over(order by customer_name) from tablename
Top Expert 2010

Commented:
RiteshShah,

Yes, but without something like an identity column, that ordering is going to be arbitrary.

Which might be OK, but might not :)

Patrick
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

this will work.



create table custTest
(
customer_name varchar(20),
 CUSTOMER_AMOUNT int
)
go

insert into custTest
select 'CUST_A', 150 union all
select 'CUST_A', 200 union all
select 'CUST_A', 100 union all
select 'CUST_B', 200 union all
select 'CUST_B', 100 union all
select 'CUST_C', 100
go

select customer_name, row_number() over(partition by customer_name order by customer_name) as rn from custTest

Open in new window

@patrick,

:)
Use the following code, you 'll reach the solution..
SELECT CUSTOMER_NAME, ROW_NUMBER() OVER(PARTITION BY(CUSTOMER_NAME) ORDER BY CUSTOMER_NAME ASC) FROM tableName

Open in new window

@waltersnowslinarnold,

same solution as mine :)
@RiteshShah

Ooops! yeah I saw ur solution only after i posted my solution..

Author

Commented:
Actually waltersnowslinarnold's is solution what I am looking for, because PARTITION clause
RiteshShah, you solution looks similar but it returns the following result set:
 CUST_A 1
CUST_A 2
CUST_A 3
CUST_B 4
CUST_B 5
CUST_C 6

RiteshShah: You solution will probably work, but I cannot create a temporarly table.
 

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial