CODEWITHSUNDEEP
sqlpostgresqlmultiple-columns
user1416065
user1416065

Reputation: 63

Table sorting based on the number of occurrences of a particular item

Here is a example table with 2 columns. 

 id | name
------------
  1 | hello  
  2 | hello  
  3 | hello  
  4 | hello  
  5 | world  
  6 | world  
  7 | sam  
  8 | sam  
  9 | sam  
 10 | ball  
 11 | ball  
 12 | bat  
 13 | bat  
 14 | bat  
 15 | bat  
 16 | bat

In the above table here is the occurrence count

hello  - 4  
world  - 2  
sam    - 3  
ball   - 2  
bat    - 5

How to write a query in psql, such that the output will be sorted from max occurrence of a particular name to min ? i.e like this

bat  
bat  
bat  
bat  
bat  
hello  
hello  
hello  
hello  
sam  
sam  
sam  
ball  
ball  
world  
world

Upvotes: 2

Views: 597

Answers (4)

Decly
Decly

Reputation: 379

You can make it with a temporal table, if the original table is named sorting:

create temp table counted as select name, count(name) from sorting group by name;

select sorting.name from sorting, counted where sorting.name = counted.name order by count desc;

Upvotes: 0

Radek Postołowicz
Radek Postołowicz

Reputation: 4774

Alternative solution using window function:

select name from table_name order by count(1) over (partition by name) desc, name;

This will avoid scanning table_name twice as in Tim's solution and may perform better in case of big table_name size.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

You can use a temporary table to get counts for all the names, and then JOIN that to the original table for sorting:

SELECT yt.id, yt.name
FROM your_table yt INNER JOIN
(
    SELECT COUNT(*) AS the_count, name
    FROM your_table
    GROUP BY name
) t
ON your_table.name = t.name
ORDER BY t.the_count DESC, your_table.name DESC

Upvotes: 4

Alexey Kurilov
Alexey Kurilov

Reputation: 438

SELECT count( ID ) cnt, NAME
FROM table_name
GROUP BY NAME
ORDER BY count( ID ) DESC;

Upvotes: -1

Related Questions