Oralce sql：I want to select the TOP 3 Records-CodePudding

I want to select the TOP 3 Records ordered desc by 'cnt'
this is top 4

a   b   c     cnt 
99  YC  市購件異常   3
99  LY  漏油  2
99  QT16    其他異常    2
99  JGSH    機構損壞    1

then select * from （）where rownum<= 3 order by cnt desc
get data

99  YC  市購件異常   3
99  LY  漏油  2
99  JGSH    機構損壞    1

i want to get

99  YC  市購件異常   3
99  LY  漏油  2
99  QT16    其他異常    2

CodePudding user response：

Try this:

SELECT T.a, T.b, T.c, T.cnt
FROM
(
  SELECT *, RANK() OVER(PARTITION BY a ORDER BY cnt DESC) RNK
  FROM TEST_TBL
) T
WHERE T.RNK <= 3

CodePudding user response：

It looks like you want to keep "duplicates" (in the cnt column) in the result.

In that case, I'd say that it is row_number analytic function that helps:

Sample data:

SQL> with test (a, b, cnt) as
  2    (select 99, 'yc'  , 3 from dual union all
  3     select 99, 'ly'  , 2 from dual union all
  4     select 99, 'qt16', 2 from dual union all
  5     select 99, 'jgsh', 1 from dual union all
  6     --
  7     select 99, 'abc' , 2 from dual  --> yet another row with CNT = 2
  8    ),

Query begins here: first rank rows (line #11), and then return the top 3 (line #15):

  9  temp as
 10    (select a, b, cnt,
 11       row_number() over (partition by a order by cnt desc) rnk
 12     from test
 13    )
 14  select * from temp
 15  where rnk <= 3;

         A B           CNT        RNK
---------- ---- ---------- ----------
        99 yc            3          1
        99 ly            2          2
        99 abc           2          3

SQL>

Because, if you use rank analytic function (as Hana suggested), you might get more than desired 3 rows (see the rnk column's values) (depending on data you work with, of course; rank works with data you posted, but - if there are more rows that share the same cnt value, it won't work any more):

 <snip>

  9  temp as
 10    (select a, b, cnt,
 11       rank() over (partition by a order by cnt desc) rnk
 12     from test
 13    )
 14  select * from temp
 15  where rnk <= 3;

         A B           CNT        RNK
---------- ---- ---------- ----------
        99 yc            3          1
        99 ly            2          2
        99 abc           2          2
        99 qt16          2          2

SQL>