-1

Sometime between yesterday and this June my hosting provider must have updated their MySQL since the following query now yields a different result than before.

I use the query as part of a union, but I have only copy/pasted the none working part - if you wonder why I'm using derived a table.

Select * from (SELECT c.SeatNum FROM signups c where c.SeatNum <= 5 ORDER BY c.SeatNum asc) d1

Earlier this query would give me the result of:

1
2
3
4
5

Now it in stead gives me:

2
5
1
3
4

If I just do a "SELECT c.SeatNum FROM signups c where c.SeatNum <= 5 ORDER BY c.SeatNum asc" I again get:

1
2
3
4
5

Why does using a derived table all of a sudden mix up my ordering - and what can I do to prevent it?

RK.
  • 973
  • 5
  • 19
  • 45
Peach2tm
  • 13
  • 2
  • 5
    " and what can I do to prevent it" - put the order by on the outer select, obviously. – Mitch Wheat Oct 16 '14 at 04:52
  • the order by needs to be on the inner select since it's part of a UNION and i dont wanna affect the order of the rest of the UNION – Peach2tm Oct 16 '14 at 05:28
  • If no order is specified (as is the case with the outer query) then there is no order. Your previous result was (more or less) fluke. Consider adding a dummy value (e.g SELECT 1 AS dummy, c.seat_num... Etc) to the subquery (or the outer query) so you'll have something to order by later (case when dummy = 1 then seat_num...) – Strawberry Oct 16 '14 at 07:17
  • Great idea with the dummy value! Thanks. – Peach2tm Oct 16 '14 at 14:43

1 Answers1

0

Check your table collation. The collation type will affect your sorting results. I generally use collation of utf8_general_ci.

panofish
  • 7,578
  • 13
  • 55
  • 96
  • It didn't help. What i dont understand is why the order by of the inner select works fine, but then the outer select messes it up. – Peach2tm Oct 16 '14 at 05:31