Select rows with highest value in one column in SQL -

in mysql, trying select 1 row each "foreign_id". must row highest value in column "time" (which of type datetime). can me how sql select statement must like? thank you! great! trying hours find solution :(

this table:

primary_id | foreign_id | name | time ---------------------------------------------------- 1          | 3          |    | 2017-05-18 01:02:03 2          | 3          | b    | 2017-05-19 01:02:03 3          | 3          | c    | 2017-05-20 01:02:03 4          | 5          | d    | 2017-07-18 01:02:03 5          | 5          | e    | 2017-07-20 01:02:03 6          | 5          | f    | 2017-07-18 01:02:03 

and result should like:

primary_id | foreign_id | name | time ---------------------------------------------------- 3          | 3          | c    | 2017-05-20 01:02:03 5          | 5          | e    | 2017-07-20 01:02:03 

i tried order intermediate result time (descending) , select first row using limit 1. cannot 1 row each foreign_id.

another try first order intermediate result time (descending) , group foreign_id. group statement seems executed before order statement (i received rows primary_id 1 , 4 result, not 3 , 5).

try this

select distinct * my_table inner join (select foreign_id, max(time) time my_table group foreign_id) b on a.foreign_id = b.foreign_id , a.time = b.time