CODEWITHSUNDEEP
sqloracle-database
David Zomada
David Zomada

Reputation: 157

Query to show top 10 rows with highest count

I have a table with all the cars that have crossed one road during one week. Now I want know what are the 10 most observed cars in that road.

My idea is:

1) Group the cars and count the number of times that they have crossed the road:

   select nplate, count('x') from observations group by nplate;

I have to do this because I can have the same car observed multiple times in the same week.

2) Order this group by count from highest to lowest.

3) Take the first 10 of those results.

But I don't know how to do the last two steps.

Thank you.

Upvotes: 2

Views: 2284

Answers (3)

David Faber
David Faber

Reputation: 12486

I'm surprised no one has given the answer using a window (analytic) function ROW_NUMBER():

SELECT nplate, observation_cnt FROM (
   SELECT nplate, COUNT(*) AS observation_cnt
        , ROW_NUMBER() OVER ( ORDER BY COUNT(*) DESC ) AS rn
     FROM observations
    GROUP BY nplate
) WHERE rn <= 10
 ORDER BY rn;

If there are two (or more) values of nplate with the same number of observations, and all have the 10th most observations, and it's important that you get all, then you'll want to use the window function RANK() instead:

SELECT nplate, observation_cnt FROM (
   SELECT nplate, COUNT(*) AS observation_cnt
        , RANK() OVER ( ORDER BY COUNT(*) DESC ) AS rn
     FROM observations
    GROUP BY nplate
) WHERE rn <= 10
 ORDER BY rn;

(It's possible you'll want DENSE_RANK() as well!)

In short, window functions give you a flexibility that the ROWNUM and FETCH FIRST solutions do not.

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

This works for Oracle 12c and above:

SELECT nplate,
       COUNT(*)
FROM   observations
GROUP  BY nplate
ORDER  BY COUNT(*) DESC
FETCH FIRST 10 ROWS ONLY;

Upvotes: 1

Andomar
Andomar

Reputation: 238078

You can order by count(*) desc. The Oracle way to limit the result to 10 rows is to use a subquery followed by where rownum < N:

SELECT  *
FROM    (
        SELECT  nplate
        ,       count(*) 
        from    observations 
        group by 
                nplate
        order by
                count(*) desc
        ) sub
WHERE   rownum <= 10

Your example uses count('x'), which counts the number of rows where 'x' is not null. That doesn't hurt but it doesn't make sense either.

Upvotes: 0

Related Questions