CODEWITHSUNDEEP
sqlt-sqlsql-server-2005
Umair
Umair

Reputation: 3243

SQL generate sequential id's based on other column(s)

Probably best I explain what I'm after with an example :)

Say I have the following table

SELECT *
INTO #OrderRanking
FROM (
    SELECT 'Jim', 1000
    UNION ALL
    SELECT 'Jim', 500
    UNION ALL
    SELECT 'Jim', 500
    UNION ALL
    SELECT 'Jim', 3000
    UNION ALL
    SELECT 'Bob', 1000
    UNION ALL
    SELECT 'Bob', 2000
    UNION ALL
    SELECT 'Bob', 500
    UNION ALL
    SELECT 'Bob', 500
    UNION ALL
    SELECT 'Fred', 500
) AS t(Customer, OrderTotal)

What I want is the following result in a SELECT statement

Customer, OrderTotal, CustomerId
'Jim', 1000, 1
'Jim', 500, 1
'Jim', 500, 1
'Jim', 3000, 1
'Bob', 1000, 2
'Bob', 2000, 2
'Bob', 500, 2
'Bob', 500, 2
'Fred', 500, 3

My actual query is a bit more complex, where the source columns is more than 2.

Upvotes: 1

Views: 76

Answers (2)

Jayvee
Jayvee

Reputation: 10875

select customer, ordertotal, dense_rank() over(order by total desc, customer asc)
from (select *, sum(ordertotal) over(partition by customer) total from  #orderranking) t

Upvotes: 1

Umair
Umair

Reputation: 3243

I am a pillock,

SELECT *, DENSE_RANK() OVER(ORDER BY Customer DESC)
FROM #OrderRanking

I think this should cover all cases!

Upvotes: 3

Related Questions