CODEWITHSUNDEEP
sqloracle-databaseoracle10g
lividbomma
lividbomma

Reputation: 3

Display only rows with the max count value

I'm trying to extract only the rows with the maximum count from the following query:

SELECT FOO, BAR, COUNT(X)
FROM TABLE1, TABLE2, TABLE3
WHERE <CONDITIONS INVOLVING THE 3 TABLES>
GROUP BY FOO, BAR;

Which generates output that looks like - 

FOO |  BAR        | COUNT(X)
 0     String 1       5
 1     String 2       3
 2     String 3       5
 3     String 4       4
 4     String 5       5

As you can see, there are 3 rows with a COUNT(X) value of 5. How would I edit this to ONLY display the rows that contain MAX(COUNT(X))? I am using Oracle SQL.

Upvotes: 0

Views: 3343

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

You can do this with analytic functions. First find the count as for each set of values, as you are already; then use a DENSE_RANK() (or RANK(), or ROW_NUMBER()) function to assign an order to each rows in your existing result set base on the COUNT() value; and finally only pick the ones with the highest rank:

SELECT foo, bar, cnt
FROM (
    SELECT foo, bar, cnt,
        DENSE_RANK() OVER (ORDER BY cnt DESC) dr
    FROM(
        SELECT foo, bar, COUNT(x) cnt
        FROM ...
        GROUP BY foo, bar
    )
)
WHERE dr = 1;

Note the DESC in the the ORDER BY, so the highest COUNT() is assigned the rank of 1.

Upvotes: 1

CristiC
CristiC

Reputation: 22698

This should work:

SELECT * FROM (
SELECT FOO, BAR, MAX(X) maxX, COUNT(X) cntX
FROM TABLE1, TABLE2, TABLE3
WHERE <CONDITIONS INVOLVING THE 3 TABLES>
GROUP BY FOO, BAR
) A
WHERE maxX = cntX;

Upvotes: 0

Related Questions