stefands Posted November 4, 2006 Share Posted November 4, 2006 Hello all,I've defined an index over 3 columns called "cprgpt" in table "country1". The columns in question being "camp", "region" and "postal". The $wherevar has the dynamic generated WHERE statement.[code]$query = "SELECT * FROM country1 $wherevar ORDER BY camp, region, postal LIMIT $offset, $limit"; [/code]The output of the SELECT statement provides the data looked for, the problem is that it's only sorted by camp, not followed by the region and then by postal codes. What could be the problem here?PS: There is no other index that has "camp" and first key. The primary key index for the table is on another column (campid).I've also read the following info:[url=http://dev.mysql.com/doc/refman/4.1/en/order-by-optimization.html]http://dev.mysql.com/doc/refman/4.1/en/order-by-optimization.html[/url] Quote Link to comment Share on other sites More sharing options...
fenway Posted November 4, 2006 Share Posted November 4, 2006 My only guess would be alpha vs numerical sorting... post some sample output. Quote Link to comment Share on other sites More sharing options...
stefands Posted November 4, 2006 Author Share Posted November 4, 2006 [tt]camp | region | postal------------------------------------------------------------------Camping Athens | Attica | GR-12136 Camping Bacchus | Attica | GR-19550 Camping Delphi | Cent. Greece | GR-33054 Camping Enjoy Lichnos | Epirus | GR-48060 Camping Hellas International | Thessaly | GR-38500 Camping Kalami Beach | Epirus | GR-46100 Camping Kokkino Limanaki | Attica | GR-19009 Camping Sikia | Thessaly | GR-38500 Camping Valtos | Epirus | GR-48060 Chrissa Camping | Cent. Greece | GR-33054 Ionion Beach | West Greece | GR-27050[/tt] Quote Link to comment Share on other sites More sharing options...
toplay Posted November 4, 2006 Share Posted November 4, 2006 The sorted output given in your last post is correct (as specified in the SQL query).But it's not really good sample data since there's not more than one camp with the same name to really tell that it's sorting it correctly. And would there ever be a camp with the same name in the same region?If the first three rows listed were all the same camp name, you'd still get them in that order. Example:camp | region | postal------------------------------------------------------------------Camping Athens | Attica | GR-12136Camping Athens | Attica | GR-19550Camping Athens | Cent. Greece | GR-33054 ...See what I mean? Maybe you meant to sort by region or zip, then by name? Quote Link to comment Share on other sites More sharing options...
stefands Posted November 4, 2006 Author Share Posted November 4, 2006 [quote]See what I mean? Maybe you meant to sort by region or zip, then by name?[/quote] :-[ :P you are abso right. I need to get away from my pc is what the problem is... I'm very sorry for being a nuisance. Thx for setting me straight!! :-X Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.