MYSQL distinct query

Question

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 distinct 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!

Answer 1

SELECT * FROM table_name WHERE foreign_id > 100

Answer 2

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.

Answer 3

You could do something like:

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

Answer 4

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.

Answer 5

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