EasyCFM.COM ColdFusion Forums / MySQL Related Issues / CF & MySQL Sorting without Order By

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: CF & MySQL Sorting without Order By

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

patrickl
11-09-2009 @ 2:03 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 2
Joined: Nov 2009

I have two tables, one is customerTable and the other is RecordTable. CustomerTable has the customer ID and five additional columns. Each column holds an ID from RecordTable. These are used to report the 5 records that are associated with the customer. To deliver the report to the user I query the table with no ORDER BY clause in the SQL thinking that it will return the results in the same manner that the customerTable delivered them. But somehow the results are being sorted numerically.

So CustomerTable's 5 columns are DayOne, DayTwo,DayThree,DayFour,DayFive. Each holding an ID from the recordTable. when I query that and run it in a list it is sorted by column. But when I then use that list to query the RecordTablke to display the content of those record IDs it is sorting them numerically.

So my select query delivers this:
16,2,40,68,8

But when I query the RecordTable with that list it returns them like this:
2,8,16,40,68

I am not using any ORDER BY. I tried usubg Order By NULL. That did not work. Why is MySQL ordering these results and how do I stop that?

I am querying like this:
ID IN ( <cfqueryparam cfsqltype="cf_sql_integer" value="#info.Day1#" />,
                    <cfqueryparam cfsqltype="cf_sql_integer" value="#info.Day2#" />,
                    <cfqueryparam cfsqltype="cf_sql_integer" value="#info.Day3#" />,
                    <cfqueryparam cfsqltype="cf_sql_integer" value="#info.Day4#" />,
                    <cfqueryparam cfsqltype="cf_sql_integer" value="#info.Day5#" />)


Thank you for any advise or input.
PL

nmiller
11-09-2009 @ 3:15 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 773
Joined: Apr 2003

Older databases used to automatically sort in the order
in which records were added, but most if not all current
systems will not guarantee a sort order.  You will likely
always want to provide a sort order in any query you
write unless you are only getting a single row.

Nathan Miller
NM Consulting

patrickl
11-10-2009 @ 4:11 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 2
Joined: Nov 2009

Nathan,
Thanks for replying.

We are on MySQL 5.0.27. That does not appear to be that old. Do you feel that version could be the reason for the issue? Is anyone aware of a work-around for this issue?

Thanks for any advise or direction that might help me resolve this issue.

Patrick

nmiller
11-11-2009 @ 8:48 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 773
Joined: Apr 2003

It's not a workaround, you always need to use a sort
order.  The dbms won't give a consistent result if you
don't.

Nathan Miller
NM Consulting


Website Designed and Developed by Pablo Varando.