SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tablename (`id` INTEGER, `GENUS` VARCHAR(5), `SPECIES` VARCHAR(10));
✓
INSERT INTO tablename (`id`, `GENUS`, `SPECIES`) VALUES
  ('0', 'Homo', 'Sapiens'),
  ('1', 'Homo', 'Habilis'),
  ('2', 'Canis', 'Familiaris'),
  ('3', 'Canis', 'Lupus'),
  ('4', 'Canis', 'Rufus');
✓
SELECT *
FROM tablename
ORDER BY COUNT(*) OVER (PARTITION BY GENUS) DESC, 
         GENUS,
         id;


id
GENUS
SPECIES


2
Canis
Familiaris

3
Canis
Lupus

4
Canis
Rufus

0
Homo
Sapiens

1
Homo
Habilis

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