SQL Query to fetch Highest Sale for each day

Question

I have a table Sale with the following data

Item_ID  Rate  Quantity  Sale_Day (YYYY-MM-DD)
--------------------------------------
P1        4       4       2013-06-02
P1        5       2       2013-06-02
P2        1       8       2013-06-02
P1        6       3       2013-06-03
P2        4       9       2013-06-03
P3        10      10      2013-06-03

I need the result as follows:

Item_ID  Total  Sale_Day (YYYY-MM-DD)
--------------------------------------
P1        26      2013-06-02
P3        100     2013-06-03

Any help would be greatly appreciated. Thanks!

Answer 1

Try

SELECT Sale_Day, Item_ID, Total 
FROM
(
SELECT Sale_Day, Item_ID, 
       SUM(Rate*Quantity) Total,
       RANK() OVER(PARTITION BY Sale_Day ORDER BY SUM(Rate*Quantity) DESC) rank
  FROM sale
 GROUP BY Sale_Day, Item_ID
) a 
 WHERE rank = 1
ORDER BY Sale_Day

Output:

|                    SALE_DAY | ITEM_ID | TOTAL |
-------------------------------------------------
| June, 02 2013 00:00:00+0000 |      P1 |    26 |
| June, 03 2013 00:00:00+0000 |      P3 |   100 |

Here is SQLFiddle demo