1

When using the following query:

SELECT id, rater_id, product_id, is_valid  
FROM `ratings` 
WHERE (`ratings`.element_id = 3151) 
ORDER BY id DESC, product_id DESC, is_valid ASC;

i would expect that the result set will first be sorted by id first, then by product_id and then by is_valid. what actually happens is that the result set is indeed ordered, but the is_valid column in the results appears to be sorted by DESC, not by ASC. tried using both ASC and DESC but the result set stays the same.

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id          | int(11)      | NO   | MUL | NULL    |                |
| product_id       | int(11)      | YES  | MUL | NULL    |                |
| value            | tinyint(4)   | YES  | MUL | NULL    |                |
| rater_id         | int(11)      | YES  |     | NULL    |                |
| comment          | varchar(350) | YES  |     | NULL    |                |
| created_at       | datetime     | YES  |     | NULL    |                |
| updated_at       | datetime     | YES  |     | NULL    |                |
| element_id       | int(11)      | YES  | MUL | NULL    |                |
| is_valid         | tinyint(1)   | YES  | MUL | 0       |                |
+------------------+--------------+------+-----+---------+----------------+
Elad Meidar
  • 774
  • 6
  • 11

1 Answers1

4

The id field is an [unique auto incremental] primary key. This means that regardless of the values product_id and is_valid has, the order of id will render the other two ordering rules useless.

What I think is that you probably don't understand that the ORDER BY clause works as a nested ordering and not as an independent ordering of the values of the columns.

Alexander
  • 23,432
  • 11
  • 63
  • 73
  • Exactly true, after digging to Hades's doorstep in the MySQL manual it came through that moving the ID order clause to the last position will solve the issue, thanks anyway, you are awesome. – Elad Meidar Sep 20 '12 at 17:30