Simple GROUP query
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
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
select customer_name, row_number() over(order by customer_name) from tablename
RiteshShah,
Yes, but without something like an identity column, that ordering is going to be arbitrary.
Which might be OK, but might not :)
Patrick
Yes, but without something like an identity column, that ordering is going to be arbitrary.
Which might be OK, but might not :)
Patrick
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
@patrick,
:)
:)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@waltersnowslinarnold,
same solution as mine :)
same solution as mine :)
@RiteshShah
Ooops! yeah I saw ur solution only after i posted my solution..
Ooops! yeah I saw ur solution only after i posted my solution..
ASKER
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.
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.
Without an identity column how do you expect the ordering for each customer to work?
Regards,
Patrick