CODEWITHSUNDEEP
mysqldistinct
Patrick
Patrick

Reputation: 315

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

Upvotes: 0

Views: 683

Answers (5)

Manuel Darveau
Manuel Darveau

Reputation: 4635

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

Upvotes: 0

Michael Madsen
Michael Madsen

Reputation: 54989

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.

Upvotes: 1

John Kane
John Kane

Reputation: 4443

You could do something like:

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

Upvotes: 0

Ashish Gupta
Ashish Gupta

Reputation: 15139

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.

Upvotes: 2

geffchang
geffchang

Reputation: 3340

SELECT * FROM table_name WHERE foreign_id > 100

Upvotes: 0

Related Questions