tags:

views:

47

answers:

2

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
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
Maybe add a "limit 1" and he will get just the one row he wants.
jabbie
**@jabbie:** He wants the most recent one per group. A `LIMIT` won't help you there. My last example will achieve that.
Andrew Moore
Exactly. Thanks Andrew. I'm trying that now but for some reason I'm getting an empty set. :/
I havent actually selected the timestamp in my fields. Do I need that selected?
**@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
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.
I think your having clause is what I need but I'm not 100% sure on this.
It won't work with the HAVING. Try the WHERE on the subquery.
Andrew Moore
Ok, I will be right back. Thanks Andrew!
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.
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