SQL - getting max value

Question

The below query gives me results for count greater than 70.

SELECT books.name, COUNT(library.staff)
FROM (library INNER JOIN books
ON library.staff = books.id)
GROUP BY library.staff,books.id
HAVING COUNT(library.staff) > 70;

How do I modify my query to get the result with the maximum count?

Answer 1

One method is order by and limit:

SELECT b.name, COUNT(l.staff) as cnt
FROM library l INNER JOIN
     books b
     ON l.staff = b.id
GROUP BY l.staff, b.name
ORDER BY cnt DESC
LIMIT 1;

I find it strange that you are grouping by two columns, but only one is in the select. However, if the query is working, then it is just looking for duplicates.

Answer 2

you can do like this

SELECT books.name, COUNT(library.staff)
FROM (library INNER JOIN books
ON library.staff = books.id)
GROUP BY library.staff,books.id
HAVING COUNT(library.staff) = (select max(library.staff) from library);

Answer 3

You can us ORDER BY() DESC and LIMIT 1

SELECT books.name, COUNT(library.staff)
FROM (library INNER JOIN books
ON library.staff = books.id)
GROUP BY library.staff,books.id
ORDER BY COUNT(library.staff) DESC
LIMIT 1

Answer 4

Really?

You join on library.staff = books.id. That's a surprising naming convention. The ID of a book equals the "staff" of a library? You should provide table definitions and some explanation with that ...

For the purpose of this answer, let's assume this is correct.

Audited query

Besides using table aliases for better readability, you can simplify in several places:

SELECT b.id, b.name, count(*) AS ct
FROM   library l
JOIN   books b ON b.id = l.staff 
GROUP  BY b.id  -- l.staff -- redundant
HAVING count(*) > 70;

• Since you are joining on b.id = l.staff, l.staff is guaranteed to be NOT NULL, so use count(*), which is a bit faster while doing the same (separate implementation of count which only counts rows without looking at columns).

• GROUP BY b.id does the same without l.staff in this case.

• But since you are grouping by b.id, not by b.name, there might be duplicate names we can only tell apart by adding id to the result - unless name is unique, which you did not specify.

Faster query

Assuming that books.id is the primary key which seems reasonable:

SELECT b.id, b.name, l.ct
FROM   books b 
JOIN  (
   SELECT staff AS id, count(*) AS ct 
   FROM   library 
   GROUP  BY 1
   ) l USING (id)
HAVING count(*) > 70;

Reading the whole table or most of it, it is typically substantially faster to aggregate first, and then join.

• Aggregate a single column in query with many columns

Test with EXPLAIN ANALYZE.

Answer

To get result(s) with maximum count, ORDER BY count(*) DESC LIMIT 1 like other answers provided is typically the fastest option - to get one winner.

To get all winners:

SELECT b.id, b.name, l.ct
FROM   books b 
JOIN  (
   SELECT staff AS id, count(*) AS ct
        , rank() OVER (ORDER BY count(*) DESC) AS rnk
   FROM   library 
   GROUP  BY 1
   ) l USING (id)
WHERE  l.rnk = 1;

Using the window function rank() here (not row_number()).
Note that you can combine aggregate functions and window functions in a single SELECT:

• Get the distinct sum of a joined table column