MySQL GROUP BY and Descending

Question

I've been trying to gather some data from a MySQL database for a while now.

The problem is that I want to group the data by objectid while getting only the newest data (Descending).

Everytime I try it, I either get an error, or it doesnt Descend.

My query at the moment is:

SELECT * FROM 'table' GROUP BY 'objectid' DESC

I've tried ordering by id or timestamp, but with an ascended result.

My question is similar to: http://stackoverflow.com/questions/7306082/mysql-using-group-by-and-desc

However the answers provided there didn't solve my problem.

Thanks in advance!

score 2 · Answer 1 · answered May 27 '13 at 10:02

2

You also have to specify ORDER BY clause for sorting:

SELECT * FROM 'table' GROUP BY 'objectid' ORDER BY timestamp DESC

answered May 27 '13 at 10:02

Vishal Suthar

17,013
3
59
105

As i've said in the question, ORDER BY also outputs ascended results. – Resitive May 27 '13 at 10:06

score 0 · Answer 2 · answered May 27 '13 at 10:01

0

your query is wrong you should make backticks instead of single quotes and you missed order by statment

try this

     SELECT * FROM `table` GROUP BY `objectid` Order by id DESC

answered May 27 '13 at 10:01

echo_Me

37,078
5
58
78

I've only used single quotes because `backticks` are used for different purposes on stackoverflow ;) – Resitive May 27 '13 at 10:05

score 0 · Answer 3 · answered May 27 '13 at 10:15

This will give you the most recent entry per ObjectId, ordered by descending time; it simply left joins to find all entries where there exists no newer entry for the same ObjectId.

SELECT a.* 
FROM table1 a
LEFT JOIN table1 b
  ON a.ObjectId=b.ObjectId 
 AND a.time < b.time
WHERE b.ObjectId IS NULL
ORDER BY time DESC

An SQLfiddle to test with.

MySQL GROUP BY and Descending

3 Answers3