How to output maximum

Question

I'm just trying to understand how to use max and count for my queries. I have the following code:

 SELECT SERVICE, COUNT(*)
 FROM SUBSCRIBERS INNER JOIN SERVICE_SUBSCRIBERS ON
 SERVICE_SUBSCRIBERS.LINE=SUBSCRIBERS.PORTID
 GROUP BY SERVICE;

Which outputs:

3WC|12
CFB|17
CWT|20
DSP|16
MSG|25

But I only want to output the maximum (MSG|25). I would also like to know how to output according to a specific condition (>15). I'm just not sure how to use the functions count and max at the same time.

Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Clockwork-Muse, Nov 05 '18 at 19:55

score 1 · Answer 1 · answered Nov 05 '18 at 19:54

You don't need to use max here - you can simply limit the number of rows returned and use order by.

Depending on the rdbms you are working with the syntax may very, but the idea is still the same:

For SQL Server, MS Access, and probably some more databases, use TOP:

SELECT TOP 1 SERVICE, COUNT(*)
FROM SUBSCRIBERS 
INNER JOIN SERVICE_SUBSCRIBERS 
    ON SERVICE_SUBSCRIBERS.LINE=SUBSCRIBERS.PORTID
GROUP BY SERVICE
ORDER BY COUNT(*);

For Oracle, use offset...fetch next (also works with sql server starting from version 2012)

SELECT SERVICE, COUNT(*)
FROM SUBSCRIBERS 
INNER JOIN SERVICE_SUBSCRIBERS 
    ON SERVICE_SUBSCRIBERS.LINE=SUBSCRIBERS.PORTID
GROUP BY SERVICE
ORDER BY COUNT(*)
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

For MySql, use Limit:

SELECT SERVICE, COUNT(*)
FROM SUBSCRIBERS 
INNER JOIN SERVICE_SUBSCRIBERS 
    ON SERVICE_SUBSCRIBERS.LINE=SUBSCRIBERS.PORTID
GROUP BY SERVICE
ORDER BY COUNT(*)
LIMIT(1)

In mysql, how would you specify that COUNT(*) is a certain value like 20. I've tried adding WHERE COUNT(*) = '20'. — bob, Nov 06 '18 at 00:43
You use the `Having` clause after the `group by` clause - `...group by service having count(*) = 20...`. — Zohar Peled, Nov 06 '18 at 04:42

score 0 · Answer 2 · answered Nov 05 '18 at 19:51

Depending on your specific database vendor, you might be able to do this:

SELECT SERVICE, CNT FROM (
 SELECT SERVICE, COUNT(*) AS CNT
 FROM SUBSCRIBERS INNER JOIN SERVICE_SUBSCRIBERS ON
 SERVICE_SUBSCRIBERS.LINE=SUBSCRIBERS.PORTID
 GROUP BY SERVICE
) WHERE ROWNUM = 1 ORDER BY CNT DESC

How to output maximum

2 Answers2