SQL ORDER BY GROUP BY

Question

I have 2 tables (ENTRIES and USERS) linked by id, I want to select all the entries from the top 5 users only, and then order them by the COUNT of individual users entries.

USERS    ENTRIES

tom      entry1
mary     entry2
jane     entry3
tom      entry4
tom      entry5

and so on.... to display as

USERS    ENTRIES

tom      entry1
tom      entry4
tom      entry5
jane     entry3
mary     entry2

my code at the moment just orders by names but does not list by COUNT of entries

SELECT  ENTRIES.entry, USERS.NAME   FROM ENTRIES, USERS 
WHERE ENTRIES.USER_ID = USERS.ID 
ORDER BY USERS.NAME 

Answer 1

Since you are using Oracle you can use windowing functions to get the total count for each user:

SELECT e.entry, 
  u.NAME,
  count(*) over(partition by u.name) Total
FROM ENTRIES e
INNER JOIN USERS u
  ON e.USER_ID = u.ID 
ORDER BY total desc, u.NAME

See SQL Fiddle with Demo.

If you then want to return only the top 5 rows, then you can use:

select entry,
  name,
  total
from 
(
  SELECT e.entry, 
    u.NAME,
    count(*) over(partition by u.name) Total,
    rownum rn
  FROM ENTRIES e
  INNER JOIN USERS u
    ON e.USER_ID = u.ID
) 
where rn <= 5
ORDER BY total desc, NAME

See SQL Fiddle with Demo