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
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
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>
