CODEWITHSUNDEEP
sqlsql-server
Rafa
Rafa

Reputation: 21

RANK() - Multiple columns in one query

I've been struggling with a query where i have to create one rank dimension for several different columns into one query.

I've tried to add multiple times the RANK() OVER clause, but it seems not to work with greater than 1 clause on the query.

Example:

John - Sales program A: 10000 - Sales program B: 123 - Sales program C: 585
Maria - Sales program A: 500 - Sales program B: 123213 - Sales program C: 432

I would need 3 columns with the rank of each Sales program.

Upvotes: 0

Views: 4683

Answers (1)

James Z
James Z

Reputation: 12318

You can have several rank() operators, for any columns you like. The question / example data is quite vague, but you can do it just like this:

select
  person,
  A,
  rank() over (order by A) as A_RANK,
  B,
  rank() over (order by B) as B_RANK,
  C,
  rank() over (order by C) as C_RANK
from
  sales

If you data is different, for example the A, B and C are in different rows instead of columns, you can do for example a CTE that uses PIVOT to turn rows into columns.

Here's also your example in SQL Fiddle.

Upvotes: 2

Related Questions