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;