CODEWITHSUNDEEP
sqlpostgresql
Lostsoul
Lostsoul

Reputation: 25999

Can I rank SQL results based on a aggregation?

I'm a new to PostgreSQL and I'm trying to learn how to display a ranking value based on a dynamic data aggregation.

Given this data:

 country     category               cat_value   Value
---------   ----------  ----------------------  ------------
      UK       Artist            Jack Maynard   794
      UK       Artist         Shannon Mathews   735
      UK       Artist            Charlie Gard   587
      UK         City                   Leeds   142
      UK         City              Manchester   264
      UK         City                  London   58
      UK        Movie                   Logan   482
      UK        Movie    Beauty and the Beast   706
      UK        Movie            Wonder Woman   721
     USA       Artist               Louis C.K   693
     USA       Artist           Michael Flynn   739
     USA       Artist              Matt Lauer   724
     USA         City               Las Vegas   790
     USA         City             Los Angeles   756
     USA         City           New York City   634
     USA        Movie          Justice League   293
     USA        Movie                      IT   423
     USA        Movie                 Get Out   200

If I want to rank by country then, I would expect it would sort the value of and provide me a ranking based only on the country. Here's an example:

 country     category               cat_value   Value   Rank
      UK       Artist            Jack Maynard   794     1
      UK       Artist         Shannon Mathews   735     2
      UK        Movie            Wonder Woman   721     3
      UK        Movie    Beauty and the Beast   706     4
      UK       Artist            Charlie Gard   587     5
      UK        Movie                   Logan   482     6
      UK         City              Manchester   264     7
      UK         City                   Leeds   142     8
      UK         City                  London   58      9
     USA         City               Las Vegas   790     1
     USA         City             Los Angeles   756     2
     USA       Artist           Michael Flynn   739     3
     USA       Artist              Matt Lauer   724     4
     USA       Artist               Louis C.K   693     5
     USA         City           New York City   634     6
     USA        Movie                      IT   423     7
     USA        Movie          Justice League   293     8
     USA        Movie                 Get Out   200     9

If I want to rank by country & category then the results would be:

 country     category               cat_value   Value   Rank
      UK         City              Manchester   264     1
      UK         City                   Leeds   142     2
      UK         City                  London   58      3
      UK        Movie            Wonder Woman   721     1
      UK        Movie    Beauty and the Beast   706     2
      UK        Movie                   Logan   482     3
      UK       Artist            Jack Maynard   794     1
      UK       Artist         Shannon Mathews   735     2
      UK       Artist            Charlie Gard   587     3
     USA         City               Las Vegas   790     1
     USA         City             Los Angeles   756     2
     USA         City           New York City   634     3
     USA        Movie                      IT   423     1
     USA        Movie          Justice League   293     2
     USA        Movie                 Get Out   200     3
     USA       Artist           Michael Flynn   739     1
     USA       Artist              Matt Lauer   724     2
     USA       Artist               Louis C.K   693     3

Is there an easy way to do this? Right now I'm using a select all and doing this in python but I thought I'd ask if there was a way to do it directly in the query.

Upvotes: 0

Views: 94

Answers (1)

Alan
Alan

Reputation: 3042

Yes, and it sounds like you are after the window and partition functions.

You don't mention if you are using a direct SQL query or an ORM like SQLalchemy.

Using a SQL query:

SELECT country, category, cat_value, Value, 
    ROW_NUMBER() OVER (
    PARTITION BY country, category
    ORDER BY Value DESC
    ) AS 'Rank'
FROM data_table

Ranking functions: Row_number() creates a unique row number for each result, rank() creates a row number where equal results will have an equal number followed by a gap, dense_rank() creates a row number where equal results will have an equal number not followed by a gap.

PARTITION BY determines how the results are split into groups

ORDER BY specifies the order inside the result sets

Upvotes: 4

Related Questions