Oracle query to find the top 2 item per group

Question

I want to right a sql query to find the top 2 item per group in oracle.
The table contains:

P_id Price GroupX
1     10    a 
2     5     a
3     5     a
4     4     b
5     8     b
6     6     b

Output should be:

P_id Price  GroupX
1     10      a
2     5       a
5     8       b
6     6       b

What I have tried is:

Select * from table
group by GroupX
order by price desc
limit 2

Answer 1

How about

 Select  rnk,P_id, Price, GroupX 
        from (
             select P_id, Price, GroupX,
                  RANK () OVER (PARTITION BY GroupX
                           ORDER BY  Price DESC
               ) AS rnk
             from table
             )
        where rnk <= 2;

Answer 2

You can use the ROW_NUMBER() analytic function:

SELECT p_id,
       price,
       GroupX
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITION BY groupX
                             ORDER BY Price DESC ) AS rn
  FROM   table_name t
)
WHERE rn <= 2;