CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
Josh Newlin
Josh Newlin

Reputation: 108

SQL how to get the top two values only

I have a query which works, however I want to store only the first two rows into variables. This is in a loop so that's where j comes from but this is the only relevant part

SELECT MNO, COUNT(MNO) AS NUM_ALBUMS
FROM PERFORM
WHERE PERFORM.SNO IN (
    SELECT DISTINCT S.ANO
    FROM SONG S
    WHERE S.SNO IN (
        SELECT DISTINCT P.SNO
        FROM PERFORM P
        WHERE j IN P.MNO
        GROUP BY P.SNO
    )
)
AND PERFORM.MNO != 1
GROUP BY MNO
ORDER BY COUNT(MNO) DESC;

This returns something similar to this

       MNO NUM_ALBUMS
---------- ----------
     2      3
     3      3
     4      2

I would like to store the top two MNOs along with the top two counts into variables to return in a print statement. Can anyone help please? I can edit with more info if necessary.

Upvotes: 1

Views: 136

Answers (2)

DCookie
DCookie

Reputation: 43533

Another possible solution:

SELECT mno, num_albums
  FROM (SELECT MNO, COUNT(MNO) AS NUM_ALBUMS
             , ROW_NUMBER() OVER (ORDER BY COUNT(MNO) DESC) rn
          FROM PERFORM
         WHERE PERFORM.SNO IN (
               SELECT DISTINCT S.ANO
                 FROM SONG S
                WHERE S.SNO IN (
                      SELECT DISTINCT P.SNO
                        FROM PERFORM P
                       WHERE j IN P.MNO
                GROUP BY P.SNO)
           AND PERFORM.MNO != 1
         GROUP BY MNO)
 WHERE rn < 3;

Upvotes: 0

Asjad Athick
Asjad Athick

Reputation: 143

You can limit the results returned by your SQL Query using the FETCH ... ONLY clause

The limit clause takes in 2 values: the offset and then number of rows required.

Ex: If you wanted rows 10-20, your limit clause would be

OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

To return the first 2 rows only, your query would have to be then

SELECT MNO, COUNT(MNO) AS NUM_ALBUMS
FROM PERFORM
WHERE PERFORM.SNO IN (
    SELECT DISTINCT S.ANO
    FROM SONG S
    WHERE S.SNO IN (
        SELECT DISTINCT P.SNO
        FROM PERFORM P
        WHERE j IN P.MNO
        GROUP BY P.SNO
    )
)
AND PERFORM.MNO != 1
GROUP BY MNO
ORDER BY COUNT(MNO) DESC
OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY

Read up here: https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1

Upvotes: 1

Related Questions