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
A:
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;
Andrew Moore
2009-08-04 01:47:53
Hi and thanks for the help. I actually edited my post to reflect this change as I realized it after I had already posted. I have a FK that I am grouping on which still leaves me with a few rows. I need to limit that to the newest timestamp
2009-08-04 01:51:05
Maybe add a "limit 1" and he will get just the one row he wants.
jabbie
2009-08-04 01:51:38
**@jabbie:** He wants the most recent one per group. A `LIMIT` won't help you there. My last example will achieve that.
Andrew Moore
2009-08-04 01:52:47
Exactly. Thanks Andrew. I'm trying that now but for some reason I'm getting an empty set. :/
2009-08-04 01:56:55
I havent actually selected the timestamp in my fields. Do I need that selected?
2009-08-04 01:57:42
**@Jim:** I've edited my question, which should work better (I don't think you need a group by for what you want).
Andrew Moore
2009-08-04 02:04:42
Thanks Andrew. I still can't get this to work though. I even tried your sub query but I don't need the where operator in there. All I want is the most recent row from that group, so if I have 5 total rows that have been rendered after the group by clause, then all I will need is the newest row from there ordered decending.
2009-08-04 02:10:35
I think your having clause is what I need but I'm not 100% sure on this.
2009-08-04 02:11:27
It won't work with the HAVING. Try the WHERE on the subquery.
Andrew Moore
2009-08-04 02:12:55
Ok, I will be right back. Thanks Andrew!
2009-08-04 02:13:43
Thanks Andrew. I have a primary key in that table and just grouped by the newest number which gives me what I need. Thanks for helping me.
2009-08-04 02:16:54
A:
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.
lc
2009-08-04 01:49:32