CODEWITHSUNDEEP
mysqlgroup-by
Latox
Latox

Reputation: 4705

mySQL group by?

Say I have 5 rows in the database, three of them have an ID of 2, two of them have an ID of 1.

The three with ID of 2 have dates, lets say for example 1st June, 2nd June, 3rd June.

The two with ID of 1 have dates, lets say for example, 1st July, 2nd July.

How do I go about selecting only 1 of the latest values, for each ID?

When currently using GROUP BY id it is returning the first row for each, not the latest added.

I want the latest one added... any idea?

Thank you.

Upvotes: 0

Views: 246

Answers (2)

Dan Grossman
Dan Grossman

Reputation: 52372

Same Kyle R from SitePoint?

Every column in your SELECT list must either appear in your GROUP BY clause or be an aggregate function, otherwise the return value is undefined by specification and MySQL is free to give you anything it wants.

Think about what grouping means. Take some set of rows, and collapse them down into a single row representing the group. In doing so, you must tell MySQL from which row each column in the representative row should come. If you want the greatest value from the group, you use MAX. If you want the smallest, you use MIN, etc.

Upvotes: 2

John Boker
John Boker

Reputation: 83729

maybe something like:

select id, max(date) 
from thetable
group by id

Upvotes: 1

Related Questions