SELECT ... ORDER BY field IN (SELECT ...) not working on duplicate field

Question

I need to sort a MySQL table after two columns, but not like a regular sort. In a chat application i want to get results sorted by timestamp field, but also grouped by their sender

CREATE TABLE messages (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sender int(10) unsigned NOT NULL,
  message char(255) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

INSERT INTO messages VALUES ('1', '2011-02-28 01:58:24', '3', '1');
INSERT INTO messages VALUES ('2', '2011-02-28 01:58:28', '3', '2');
INSERT INTO messages VALUES ('3', '2011-02-28 01:58:35', '1', '5');
INSERT INTO messages VALUES ('4', '2011-02-28 01:58:36', '2', '7');
INSERT INTO messages VALUES ('5', '2011-02-28 01:58:38', '3', '3');
INSERT INTO messages VALUES ('6', '2011-02-28 01:58:39', '2', '8');
INSERT INTO messages VALUES ('7', '2011-02-28 01:58:40', '1', '6');
INSERT INTO messages VALUES ('8', '2011-02-28 01:58:41', '3', '4');

So:

ORDER BY timestamp, sender is useless

ORDER BY sender, timestamp is pretty ok, but it will not show older messages first

SELECT timestamp, sender, message FROM messages ORDER BY sender IN (SELECT DISTINCT sender FROM messages ORDER BY timestamp), timestamp;

does not work as i suppose it must work. And here is what i expected from this query (ofc is not what i get from this query :P):

+---------------------+--------+---------+
| timestamp           | sender | message |
+---------------------+--------+---------+
| 2011-02-28 01:58:24 |      3 | 1       |
| 2011-02-28 01:58:28 |      3 | 2       |
| 2011-02-28 01:58:38 |      3 | 3       |
| 2011-02-28 01:58:41 |      3 | 4       |
| 2011-02-28 01:58:35 |      1 | 5       |
| 2011-02-28 01:58:40 |      1 | 6       |
| 2011-02-28 01:58:36 |      2 | 7       |
| 2011-02-28 01:58:39 |      2 | 8       |
+---------------------+--------+---------+

Any ideas? links? tips? anything? Thanks in advance, any questions are welcome

Could you state exactly what the query is supposed to achieve. You've said you want to do an irregular sort and give two examples that don't do what you want, but you don't describe what you actually want and trying to infer it from the table supplied is difficult. — Hamish, Feb 28 '11 at 00:22
last table is what i want to get, the result of the perfect query :D — SYNCRo, Feb 28 '11 at 00:25
just need to get all messages, grouped by sender, and sorted by timestamp, but not sorted by sender and timestamp — SYNCRo, Feb 28 '11 at 00:26
I think your going to need to add a column to capture the ordering information. This is basically the same question but with recursive queries. http://stackoverflow.com/questions/4994716/how-to-preserve-order-of-children-to-appear-after-their-parents. — nate c, Feb 28 '11 at 00:53
can't you just put in a specific sort order in your order by as in : order by sender asc, timestamp asc — Stefan H, Feb 28 '11 at 01:04

a1ex07 · Accepted Answer · 2011-02-28T00:46:31.783

1

Will it work for you ORDER BY sender asc, timestamp desc ?

UPDATE
You probably need something like this:

SELECT a.timestamp, a.sender, a.message 
FROM messages a
INNER JOIN
 (SELECT b.sender_id, MIN(b.timestamp) as timestamp 
  FROM messages b GROUP BY sender
 )c ON (c.sender = a.sender)
ORDER BY c.timestamp, a.sender

edited Feb 28 '11 at 00:46

answered Feb 28 '11 at 00:23

a1ex07

36,826
12
90
103

nope, because sorting by sender ASC will not give me first messages timestamps, will give me first senders – SYNCRo Feb 28 '11 at 00:32
I think the updated version is what you wanted (sorting by minimum timestamp, then by sender) – a1ex07 Feb 28 '11 at 00:48
1054 - Unknown column 'b.sender_id' in 'field list' – SYNCRo Feb 28 '11 at 00:48
I meant `sender`. Sorry for misprint. changed – a1ex07 Feb 28 '11 at 00:48
after removing those _id from query, the result is exactly what i was expect to see, Thank you! – SYNCRo Feb 28 '11 at 00:49
just figured out that `ORDER BY c.timestamp, a.sender` must be in fact `ORDER BY c.timestamp, a.timestamp`. I had some problems with your ORDER BY clause with some subsequent LEFT JOINs on that query – SYNCRo Feb 28 '11 at 03:04

score 0 · Answer 2 · answered Feb 28 '11 at 00:21

0

SELECT * FROM messages ORDER BY sender, timestamp ASC;

Will sort by sender, then by timestamp giving you each senders data, sorted by timestamp. In MySQL when you use multiple sorts it just sorts by one, then the other.

What you are attempting to achieve is.. well, impossible, you cannot group the senders if they are not in order with the sender ideas.. what I've given is the closest you'll get in MySQL

answered Feb 28 '11 at 00:21

Rabbott

4,282
1
30
53

ok, what about `SELECT * FROM countries ORDER by iso_code IN ('UK', 'US') desc, iso_code`, there are something like two sorts based on a single field... something like some favorites results. (query copied from [MySQL SELECT Syntax page](http://dev.mysql.com/doc/refman/5.0/en/select.html) – SYNCRo Feb 28 '11 at 00:29

SELECT ... ORDER BY field IN (SELECT ...) not working on duplicate field

2 Answers2