如何获取mysql中每个组的最低值

Question

I want to get lowest value of each group from two table

Table Are Below

    Table 1                         Table 2         
|   GPN |   Amt |           |   GPN |   Date        |
|   A   |   10  |           |   A   |   2016-09-10  |
|   A   |   15  |           |   A   |   2016-09-18  |
|   A   |   20  |           |   B   |   2016-09-10  |
|   A   |   25  |           |   B   |   2016-09-11  |
|   A   |   30  |           |   B   |   2016-09-12  |
|   B   |   20  |           |   C   |   2016-10-12  |
|   B   |   40  |           |   C   |   2016-10-13  |
|   B   |   60  |           |   C   |   2016-10-14  |
|   B   |   80  |           |   D   |   2016-09-10  |
|   B   |   100 |           |   D   |   2016-10-13  |
|   C   |   3   |                           
|   C   |   6   |                           
|   C   |   9   |                           
|   C   |   12  |                           
|   C   |   15  |                           
|   D   |   7   |                           
|   D   |   10  |                           
|   D   |   13  |                           
|   D   |   16  |                           
|   D   |   19  |                           
|   D   |   22  |                           

How i want that value

For Example

Date = 2016-09-10,

On That how many GPN are there so i have to get every GPN's Lowest Amt

So Result Will be like this

    Result          
|   GPN |   Amt |
|   A   |   10  |
|   B   |   20  |
|   D   |   7   |

I have tried by using ASC LIMIT 1 so obviously it will show only one raw but but i have no idea how to do that.

and i did it with php loop but i am looking if it is possible in mysql query so that will awesome.

Answer 1

Inner join and group by

select table1.GPN, min(table2.Amt) 
from table1 
inner join table2 on table1.GPN= table2.GPN
where date(table2.date )  = str_to_date('2016-09-10', '%Y-%m-%d') 
group by table1.GPN