Reado Reado - 4 months ago 11
MySQL Question

MySQL - Query returning results in wrong order

The following MySQL queries below are returning results in different orders despite the "ORDER BY" being the same for both queries:

TABLE STRUCTURE

+-----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+----------------+
| image_id | int(10) | NO | PRI | NULL | auto_increment |
| property_id | int(10) | NO | | 0 | |
| image_title | text | NO | | NULL | |
| image_title_id | int(10) | NO | | 0 | |
| image_index | smallint(3) | NO | | 0 | |
| image_version | tinyint(1) | NO | | 2 | |
| image_landscape | tinyint(1) | NO | | 1 | |
| image_visible | tinyint(1) | NO | | 1 | |
| image_type | tinyint(1) | NO | | 3 | |
+-----------------+-------------+------+-----+---------+----------------+


TEST 1

Query:

SELECT image_id, room_text
FROM property_record_images
INNER JOIN property_data_rooms ON property_record_images.image_title_id = property_data_rooms.room_id
WHERE property_id = 1029
ORDER BY image_index


Result:

+----------+-----------------+
| image_id | room_text |
+----------+-----------------+
| 2042 | Front elevation |
| 2043 | Garden to rear |
| 2044 | Kitchen |
| 2045 | Breakfast area |
| 2046 | Lounge |
| 2047 | Master bedroom |
| 2048 | Studio |
+----------+-----------------+


TEST 2

Query:

SELECT GROUP_CONCAT(CONCAT(property_record_images.image_id) SEPARATOR '|')
FROM property_record_images
INNER JOIN property ON property_record_images.property_id = property.property_id
WHERE property_record_images.property_id = 1029
ORDER BY image_index


Result:

+---------------------------------------------------------------------+
| GROUP_CONCAT(CONCAT(property_record_images.image_id) SEPARATOR '|') |
+---------------------------------------------------------------------+
| 2048|2047|2044|2045|2046|2043|2042 |
+---------------------------------------------------------------------+


This is occurring with random records (different "property_id") so it's not an simple as just reversing the ORDER BY for the second query.

Any idea why this is happening and where I have gone wrong with the query?

Answer

see http://dev.mysql.com/doc/refman/...tions.html#function_group-concat:

I think you should get ordered group concat by:

SELECT GROUP_CONCAT(CONCAT(property_record_images.image_id) ORDER BY image_index SEPARATOR '|')
FROM property_record_images
INNER JOIN property ON property_record_images.property_id = property.property_id
WHERE property_record_images.property_id = 1029
ORDER BY image_index
Comments