CODEWITHSUNDEEP
mysql
Jim
Jim

Reputation:

How do I use group by showing the newest row of data

Can someone please tell me how to use the group by clause, grouping by one of the keys in the table but yet having the newest timestamp at the top? I have multiple rows of data but I only want to show the newest row

Upvotes: 0

Views: 783

Answers (2)

lc.
lc.

Reputation: 116438

I think you're looking for the ORDER BY clause.

SELECT Foo.Bar, Foo.SomeTimestamp
FROM Foo
ORDER BY Foo.SomeTimestamp DESC

If you're grouping by a column, you're probably returning aggregate data. If the timestamp is unique for each row of aggregate data, you may need to use the MAX function (or the like) to return a single timestamp for each group. For example:

SELECT Foo.TypeID, SUM(Foo.Price) AS Price, MAX(Foo.OrderDate) AS LastOrder
FROM Foo
GROUP BY Foo.TypeID
ORDER BY MAX(Foo.OrderDate) DESC

If you only want the first row, you can use the LIMIT clause:

SELECT Foo.Bar, Foo.SomeTimestamp
FROM Foo
ORDER BY Foo.SomeTimestamp DESC
LIMIT 0, 1

This starts at row 0 and returns at most 1 row.

Upvotes: 1

Andrew Moore
Andrew Moore

Reputation: 95314

If you want only the most recent one per group:

SELECT somefield
FROM table t1
WHERE timestamp = (SELECT MAX(timestamp)
                   FROM table t2
                   WHERE t1.somefield = t2.somefield);

Or just the latest most recent one:

SELECT somefield
FROM table
GROUP BY somefield
ORDER BY MAX(timestamp) DESC
LIMIT 1;

Upvotes: 1

Related Questions