CODEWITHSUNDEEP
mysqlsql-order-by
Aleem Ahmad
Aleem Ahmad

Reputation: 2539

MySQL ORDER BY clause not behaving correctly

I have a query in which I am sorting the data on date and time column. I have five records with same date and time. All the records are coming fine according to the sorting and primary key. But the last record I add jumps to top of the position. And I have no clue why is it happening.

My query is:

SELECT table1.* , table2.name AS name1, table2.date AS date1, 
       table2.start_time AS time1, table2.end_time AS time2
FROM table1 LEFT JOIN
     table2
     ON table2.id = table1.id1
WHERE table1.id2 = '604'
ORDER BY table1.date ASC, table1.start_time ASC

also the record screenshot is attached. The top record has the latest id but it is coming on top. All these records in the image has the same date and time.

enter image description here

Upvotes: 0

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I am not 100% sure why you are confused. If the date and time columns on the five rows all have the same value, then they can be returned in any order.

If you also want them ordered by the id, then include that in the order by:

ORDER BY table1.date ASC, table1.start_time ASC, table1.id

You seem to have some notion of a "stable" sort for order by. A stable sort is one that keeps the original ordering of the data, for keys that have the same value. However, that is simply not possible in SQL, because tables represent unordered sets. There is no ordering without an order by.

Upvotes: 1

Related Questions