How to output maximum

4170 views sql
0

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.

answered question

What rdbms are you working with?

2 Answers

5

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

posted this
6

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)

posted this

Have an answer?

JD

Please login first before posting an answer.