Databases
StackOverflow2013
Postsdbo
POSELECT ... ORDER BY field IN (SELECT ...) not working on duplicate field
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSELECT ... ORDER BY field IN (SELECT ...) not working on duplicate field
    primarykey
    data
    text
    <p>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</p> <pre><code>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'); </code></pre> <p>So:</p> <p><strong>ORDER BY timestamp, sender</strong> is useless</p> <p><strong>ORDER BY sender, timestamp</strong> is pretty ok, but it will not show older messages first</p> <pre><code>SELECT timestamp, sender, message FROM messages ORDER BY sender IN (SELECT DISTINCT sender FROM messages ORDER BY timestamp), timestamp; </code></pre> <p>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):</p> <pre><code>+---------------------+--------+---------+ | 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 | +---------------------+--------+---------+ </code></pre> <p>Any ideas? links? tips? anything? Thanks in advance, any questions are welcome</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USSYNCRo
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COCould 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.
      singulars
      1. POSELECT ... ORDER BY field IN (SELECT ...) not working on duplicate field
      1. USHamish
    2. COlast table is what i want to get, the result of the perfect query :D
      singulars
      1. POSELECT ... ORDER BY field IN (SELECT ...) not working on duplicate field
      1. USSYNCRo
    3. COjust need to get all messages, grouped by sender, and sorted by timestamp, but not sorted by sender and timestamp
      singulars
      1. POSELECT ... ORDER BY field IN (SELECT ...) not working on duplicate field
      1. USSYNCRo
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload