Order results by number of other rows with the same column value?-CodePudding

I have a table with the columns id, GENUS, SPECIES. The entries of the table many have multiple of the same GENUS but one unique SPECIES per.

id, GENUS, SPECIES
0 ,  Homo, Sapiens
1 ,  Homo, Habilis
2 , Canis, Familiaris 
3 , Canis, Lupus
4 , Canis, Rufus

I would like to generate a query where the results are ordered by the rows of the most numerous GENUS first.

id, GENUS, SPECIES
2 , Canis, Familiaris 
3 , Canis, Lupus
4 , Canis, Rufus
0 ,  Homo, Sapiens
1 ,  Homo, Habilis

It seems that I could need to first calculate the unique values of the column, count the number of each, make a new column with that value for each row, then sort by that row, and select the original columns?

CodePudding user response：

Use COUNT() window function in the ORDER BY clause:

SELECT *
FROM tablename
ORDER BY COUNT(*) OVER (PARTITION BY GENUS) DESC, 
         GENUS, -- in case more than 1 GENUS have the same rows keep the rows of the GENUS together
         id;    -- sort the rows of each GENUS by id

See the demo.