MYSQL distinct query

This is my example of my table:

id | name | foreign_id |
-------------------------
1    a       100
2    b       100
3    c       100
4    d       101
5    a       102
6    b       102
7    c       102

I would like to get the dis开发者_Go百科tinct file with the latest foreign_id (bigger number but not necessarily biggest). In this example, it would be row with id 4,5,6,7. Anyone has any idea? Thank you very much!

Could you try something like below :-

SELECT Id,Table1.Name,Table1.Fid FROM Table1 INNER JOIN 
(SELECT Name,Max(FId) AS FId FROM Table1 Group By Name)
Table2 ON Table1.FId=Table2.FId AND Table1.Name=table2.Name

This works in Sql Server atleast. Please check in MySQL. Sorry, I dont have MySQL to test this.

Sounds like you just want this:

SELECT name, MAX(foreign_id)
FROM table
GROUP BY name;

If you need the ID (I'm guessing you won't, since name and foreign_id should probably be unique, making the ID column unnecessary), I think MySQL will allow you to get that by just adding that column to the SELECT list - although this is non-standard SQL. If you want standard SQL, then you want something like Ashish wrote.

SELECT * FROM table_name WHERE foreign_id > 100

You could do something like:

select *
from table_name
where max_id_you_want = (select max(id_you_want) from table_name)

SELECT * FROM table
GROUP BY name
having MAX(foreign_id);