User97798 User97798 - 4 months ago 10
SQL Question

How to get Lowest value of each group in mysql

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

Inner join and group by

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