SQL query for this operation?-CodePudding

Can someone please help me with this SQL query? I used a GROUP BY Category but how can I list the top two of revenue?

So i am using the following query for now -- SELECT Product FROM table_name ORDER BY Revenue DESC, GROUP BY Category, LIMIT 2;

Please let me know if this would work?

CodePudding user response：

If your database supports dense rank:

Select * 
from 
  (Select tbl.*,
          Dense_rank() over (partition by category order by revenue desc) as rnk
   From yourtable TBL
) Tbl2
Where tbl2.rnk<=2

CodePudding user response：

you need row_number as follows

SELECT *
FROM  (SELECT product,
              category,
              Sum(revenue) as sales,
              Row_number()
                OVER(
                  partition BY category
                  ORDER BY  sum([Sales]) desc) AS RowNum
       FROM   table
       GROUP  BY product,
                 category) a
WHERE  rownum IN ( 1, 2 )

CodePudding user response：

Select revenue from table name where product name = free4.5, free5.0;