Skip to content
Advertisement

How to list 3 largest numbers from table in SQL?

I’m trying to unite two bits of SQL codes and list three largest numbers from it. Here is table relation diagram

Query #1:

SELECT MAX(d) AS deliveries
FROM (SELECT o.rider_id, COUNT(o.rider_id) d
FROM orders o
GROUP BY o.rider_id) AS alias; 

Query #2:

SELECT first_name, last_name
FROM users;

My final code shows the full list of all first and second names, simply adding largest number to all of them from orders table. How can I modify it to make it show three names with its largest delivery count?

SELECT u.first_name, u.last_name, MAX(d) AS deliveries
FROM (SELECT o.rider_id, COUNT(o.rider_id) d
FROM orders o
GROUP BY o.rider_id) AS alias
JOIN users u
GROUP BY u.first_name, u.last_name; 

Advertisement

Answer

If you want exactly the top 3 (or less if there are less than 3) then use order by the attribute you want, and use limit:

SELECT u.first_name, u.last_name, MAX(d) AS deliveries
FROM (SELECT o.rider_id, COUNT(o.rider_id) d
FROM orders o
GROUP BY o.rider_id) AS alias
JOIN users u
GROUP BY u.first_name, u.last_name
ORDER BY deliveries DESC
LIMIT 3;
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement