1

i have a table of of employee_sale

id  product   quantity
3    y        6
1    x        2
1    y        5
2    x        4
3    x        3
3    y        5
3    X        5
4    y        3
4    y        4

i want to select list of employee id, product and second maximum quantity against all products.

please help with necessary sql query

thanks

Raging Bull
  • 18,593
  • 13
  • 50
  • 55
user3300467
  • 15
  • 5
  • And, what have you tried? – Raging Bull May 14 '14 at 16:51
  • @RagingBull i tried this but this query is not working perfactly. select id,product, MAX(quantity) from employee_sale where quantity not in(select max(quantity) from employee_sale ) group by id,product – user3300467 May 14 '14 at 16:58

2 Answers2

1

I'd use the rank() function for this:

SELECT id, product, quantity
FROM   (SELECT id, product, quantity, 
               RANK() OVER (PARTITION BY id, product ORDER BY quantity DESC) 
               AS rk
        FROM   employee_sale)
WHERE  rk = 2
Mureinik
  • 297,002
  • 52
  • 306
  • 350
1

Two alternatives:

  1. Using ROW_NUMBER():

    SELECT id,product,quantity FROM
(SELECT *,ROW_NUMBER() OVER(PARTITION BY product ORDER BY quantity DESC) as RN
FROM employee_sale) T
WHERE RN=2

  2. Using RANK():

    SELECT id,product,quantity FROM
(SELECT *, RANK() OVER (PARTITION BY product ORDER BY quantity DESC) as Rnk
FROM employee_sale) T
WHERE Rnk=2

Result:

ID  PRODUCT  QUANTITY
2   x        4
1   y        5
Raging Bull
  • 18,593
  • 13
  • 50
  • 55
  • Both valid options. @OP, please note that if you decide to use ROW_NUMBER(), it assigns the row number arbitrarily for duplicate values. See also: http://stackoverflow.com/a/7747406/62640 – Sam Trost May 14 '14 at 17:21