LinuxQuestions.org
Did you know LQ has a Linux Hardware Compatibility List?
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 10-18-2009, 10:14 PM   #1
hbar
Member
 
Registered: Dec 2007
Location: Canada
Distribution: Debian
Posts: 50

Rep: Reputation: 15
MySQL results arbitrarily ordered


Maybe someone more MySQL-savvy can answer this for me. If I have a query:
Code:
SELECT `SomeField` FROM `SomeTable` WHERE `key`='value1' OR `key`='value2' OR `key`='value3';
I can run the query and get a result with 3 lines, but the results are arbitrarily ordered. Can I specify that I want the results in the order they are specified in the WHERE clause? (not necessarily using all those ORs, just as example)

??
 
Old 10-18-2009, 10:36 PM   #2
nadroj
Senior Member
 
Registered: Jan 2005
Location: Canada
Distribution: ubuntu
Posts: 2,539

Rep: Reputation: 58
first, i dont know your skill level with SQL, but i imagine you arent actually looking for the "ORDER BY" clause.

ive never used mySQL, and also its been a while since ive used SQL. however, i have read that without an ORDER BY clause, the order of the results is "undefined". that is, one implementation of SQL may returned the rows in the order they were inserted, while another one may return them in a randomized order (unlikely, but possible and for illustration purposes).

if you arent looking for the "ORDER BY" clause, and you want to force some "ORDER", then its obvious that you should be looking for "ORDER BY".

Last edited by nadroj; 10-18-2009 at 10:37 PM.
 
Old 10-18-2009, 11:24 PM   #3
paulsm4
Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
Hi -

SQL is based on "set theory" and, by definition, the "tuples" (what you and I normally think of as "rows" or "records") HAVE NO INTRINSIC "order" with respect to each other.

However, in practice you *do* often wish some kind of order: alphabetic (by last name), numeric (by postal code), etc.

Hence the "order by" clause nadroj spoke of. For example:
Quote:
SELECT a,b,c FROM mytable WHERE a > xyz ORDER BY c
 
Old 10-19-2009, 05:52 AM   #4
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Lenny/Squeeze/Wheezy/Sid
Posts: 4,053

Rep: Reputation: 484Reputation: 484Reputation: 484Reputation: 484Reputation: 484
You cannot have it ordered according to the orde in the WHERE clause. If you query for key=3 or key=17 or key=1 there is no obvious way to get the records back in the sequence 3,17,1.

There must be at least one field which you can order ascending or descending. You can order by one field and then by another field. ORDER BY lastname, firstname.

If you want to order randomly according to you query you could UNION:
select field from table where key=3 UNION select field from table where key=17 UNION ...

That will give the records in the sequence of the select statements in the UNION.

jlinkels
 
Old 10-19-2009, 09:29 AM   #5
hbar
Member
 
Registered: Dec 2007
Location: Canada
Distribution: Debian
Posts: 50

Original Poster
Rep: Reputation: 15
I am aware of ORDER BY, and that's not what I'm looking for.

Quote:
Originally Posted by paulsm4 View Post
SQL is based on "set theory" and, by definition, the "tuples" (what you and I normally think of as "rows" or "records") HAVE NO INTRINSIC "order" with respect to each other.
I think this answers my question. I guess I will have to re-think the problem.
 
Old 10-19-2009, 09:33 AM   #6
hbar
Member
 
Registered: Dec 2007
Location: Canada
Distribution: Debian
Posts: 50

Original Poster
Rep: Reputation: 15
Maybe I should add that I'm currently doing 3 separate queries, each with one item in the WHERE clause, but the overhead for executing queries makes this slow if there are many more (than 3) items.
 
Old 10-19-2009, 10:51 AM   #7
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
Maybe you can describe the problem that you're facing and we can think with you.
 
Old 10-19-2009, 11:41 AM   #8
hbar
Member
 
Registered: Dec 2007
Location: Canada
Distribution: Debian
Posts: 50

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by Wim Sturkenboom View Post
Maybe you can describe the problem that you're facing and we can think with you.
Without going into details, my problem goes something like this. Say I have a table with two columns:
Code:
Key, HugeData
where "Key" is a key (duh) and "HugeData" contains large BLOBs of data. I need to select a number of rows (could be 2, could be 1000) from the table, sort them according to some rules (which aren't in the database) and then display them in order. The sorting is recursive and I think shuffling around the HugeData values will create a bottleneck, so I do it in two steps: select the Keys (which are small), sort them, and then query again to select the HugeData in the order of the newly-sorted Keys.

The second query is what I'm asking about here. My current solution is to loop over the sorted keys and do one query each, but this is itself a bottleneck.
 
Old 10-19-2009, 11:45 AM   #9
Lordandmaker
Member
 
Registered: Sep 2005
Location: London, UK
Distribution: Debian
Posts: 258

Rep: Reputation: 38
jinkels UNION hint looks appropriate; iterate through the ordered list of keys, creating a long series of UNION'd queries to get the HugeDatas out.
 
Old 10-19-2009, 12:01 PM   #10
bgeddy
Senior Member
 
Registered: Sep 2006
Location: Liverpool - England
Distribution: slackware64 13.37 and -current, Dragonfly BSD
Posts: 1,810

Rep: Reputation: 227Reputation: 227Reputation: 227
One possible way of ordering the results as per the order of query values is like this :
Code:
SELECT `SomeField` FROM `SomeTable` WHERE find_in_set(`key`,'value1,value2,value3') order by find_in_set(`key`,'value1,value2,value3');
 
Old 10-19-2009, 12:25 PM   #11
hbar
Member
 
Registered: Dec 2007
Location: Canada
Distribution: Debian
Posts: 50

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by Lordandmaker View Post
jinkels UNION hint looks appropriate; iterate through the ordered list of keys, creating a long series of UNION'd queries to get the HugeDatas out.
Aha! I think this will work! I will try when I get a few minutes!
 
Old 10-19-2009, 06:10 PM   #12
hbar
Member
 
Registered: Dec 2007
Location: Canada
Distribution: Debian
Posts: 50

Original Poster
Rep: Reputation: 15
It does work, they come out in the right order. I have never used "union" before. Thanks to everyone.
 
Old 10-19-2009, 06:40 PM   #13
AnanthaP
Member
 
Registered: Jul 2004
Location: Chennai, India
Distribution: UBUNTU 5.10 since Jul-18,2006 on Intel 820 DC
Posts: 607

Rep: Reputation: 127Reputation: 127
Another way.

Put the data of the WHERE clause into another table - say TABLE_B. and rewrite your query thus -

SELECT `SomeField` FROM `SomeTable` a WHERE a.`key`=TABLE_B.'value1' ORDER BY `SomeField`;

This would remove the long query.

By the way, I didn't understand the need for the backquote ` in the SQL statement.

End
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
I'm trying to get PHP to filter results from a MySQL query!!! Tom "Techno" Earl Programming 4 07-18-2011 06:49 AM
mysql: trying to limit results seems to expand them -or- need help with a query BrianK Programming 1 04-01-2009 02:09 PM
Finding particular ranges of results in MySQL resetreset Programming 6 01-24-2009 11:59 PM
MySQL results to html forms using PHP xemous Programming 3 08-15-2005 03:27 PM
GIMP - Rotate image arbitrarily Micro420 Linux - Software 2 11-03-2003 02:52 AM


All times are GMT -5. The time now is 09:35 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration