All entries for Tuesday 02 November 2010

November 02, 2010

SQL issue using GROUP BY and ORDER BY together

I am having an issue with SQL not behaving as I expect it to. (MySQL 5.1.41 although it shouldn’t be relevant.) I am hoping I’ve missed something really obvious but I just can’t see it at the moment, hence blog post.

I am dealing with submissions to a node with some data.

CREATE TABLE test (time int, nodeid int, data varchar(50));

DESCRIBE test;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| time   | int(11)     | YES  |     | NULL    |       |
| nodeid | int(11)     | YES  |     | NULL    |       |
| data   | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

time is the primary key for purposes of this explanation
nodeid is a foreign key
data is some information associated with a submission

INSERT INTO test (time, nodeid, data) VALUES (1, 1, 'ONE');
INSERT INTO test (time, nodeid, data) VALUES (2, 2, 'TWO');
INSERT INTO test (time, nodeid, data) VALUES (3, 3, 'THREE');
INSERT INTO test (time, nodeid, data) VALUES (4, 4, 'FOUR');
INSERT INTO test (time, nodeid, data) VALUES (5, 1, 'FIVE');
INSERT INTO test (time, nodeid, data) VALUES (6, 2, 'SIX');
INSERT INTO test (time, nodeid, data) VALUES (7, 1, 'SEVEN');
INSERT INTO test (time, nodeid, data) VALUES (8, 3, 'EIGHT');

SELECT * FROM test;
+------+--------+-------+
| time | nodeid | data  |
+------+--------+-------+
|    1 |      1 | ONE   |
|    2 |      2 | TWO   |
|    3 |      3 | THREE |
|    4 |      4 | FOUR  |
|    5 |      1 | FIVE  |
|    6 |      2 | SIX   |
|    7 |      1 | SEVEN |
|    8 |      3 | EIGHT |
+------+--------+-------+

Node 1 has three submissions: ONE, FIVE, SEVEN
Node 2 has two submissions: TWO, SIX
Node 3 has two submissions: THREE, EIGHT
Node 4 has one submission: FOUR

We want to show the submissions, most recent first:

SELECT time, nodeid, data FROM test ORDER BY time DESC;
+------+--------+-------+
| time | nodeid | data  |
+------+--------+-------+
|    8 |      3 | EIGHT |
|    7 |      1 | SEVEN |
|    6 |      2 | SIX   |
|    5 |      1 | FIVE  |
|    4 |      4 | FOUR  |
|    3 |      3 | THREE |
|    2 |      2 | TWO   |
|    1 |      1 | ONE   |
+------+--------+-------+

But now we want to show only the most recent submission for each node, so we try:

SELECT time, nodeid, data FROM test GROUP BY nodeid ORDER BY time DESC;
+------+--------+-------+
| time | nodeid | data  |
+------+--------+-------+
|    4 |      4 | FOUR  |
|    3 |      3 | THREE |
|    2 |      2 | TWO   |
|    1 |      1 | ONE   |
+------+--------+-------+

That’s not what we want! The most recent submissions are 8, 7, 6 and 4.

Among other things I have tried:

SELECT time, nodeid, data FROM test GROUP BY nodeid ORDER BY MAX(time) DESC;

(returns first submissions ordered by last submission time)

SELECT MAX(time), nodeid, data FROM test GROUP BY nodeid ORDER BY MAX(time) DESC;

(returns first submission data but last submission time)

SELECT time, nodeid, data FROM test GROUP BY nodeid HAVING time = MAX(time) ORDER BY time DESC;

(returns only one result)

SELECT time, MAX(time) timemax, nodeid, data FROM test WHERE time = timemax GROUP BY nodeid ORDER BY time DESC;

(error: can’t use aggregation functions in WHERE clause)

Is there anyone who can help me out and suggest how to get the result set I need?


New blog location

After a hiatus of several years, I’ve started blogging again at blog.draknek.org.

My website

Looking for more information about Alan Hazelden? Follow me on Twitter or go to my website.

November 2010

Mo Tu We Th Fr Sa Su
Oct |  Today  | Dec
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30               

Search this blog

Blog archive

Loading…

Most recent comments

  • Knife game: No actual cutlery involved. A single player starts with the "knife" by clasping his or h… by Noyb on this entry
  • My friends and I have taken to this one very silly game lately that's probably altogether too insula… by Ian S. on this entry
  • Great, thanks for those! That first one I would know by the name of Wink Murder, usually with a sing… by Alan Hazelden on this entry
  • I don't know the names for these games, so I'll make them up: Sniper. Randomly choose who is the sin… by zep on this entry
  • I recommend checking out Project Sprouts from Luke Bayes. It makes obtaining and configuring the fla… by Duncan Beevers on this entry

Tags

Not signed in
Sign in

Powered by BlogBuilder
© MMXIV