gopi nath pandraju gopi nath pandraju - 3 months ago 15
MySQL Question

How to get minimum and second minimum rows for each group

i have a table like

fruit ex.Date price
banana 01/08/2016 57
banana 05/08/2016 60
banana 12/07/2016 90
grape 03/08/2016 40
grape 06/08/2016 20
grape 08/07/2016 10
apple 25/07/2016 80
apple 18/06/2016 20


now i need two tables from mytable
1st table like (based on min date in each fruit group)

fruit ex.Date price
banana 12/07/2016 90
grape 08/07/2016 10
apple 18/06/2016 80


then 2nd table (based on next date in each group)

fruit ex.Date price
banana 01/08/2016 57
grape 03/08/2016 40
apple 25/07/2016 80

Answer

You can get the first output from through the query given below:

SELECT 
myTable.*
FROM myTable
INNER JOIN 
(
    SELECT 
    fruit,
    MIN(`ex.Date`) min_date
    FROM myTable
    GROUP BY fruit
) AS t
ON myTable.fruit = t.fruit AND myTable.`ex.Date` = t.`ex.Date`

In order to get the second output:

SELECT 
myTable.*
FROM myTable
INNER JOIN 
(
    SELECT 
    S.fruit,
    MIN(S.`ex.Date`) min_date
    FROM myTable S
    WHERE `ex.Date` > (SELECT MIN(T.`ex.Date`) FROM myTable T WHERE S.fruit = T.fruit)
    GROUP BY S.fruit
) AS t
ON myTable.fruit = t.fruit AND myTable.`ex.Date` = t.`ex.Date`;