Re: Order by in clause

2010-11-09 Thread Joeri De Backer 
On Tue, Nov 9, 2010 at 10:09 AM, Mark Goodge m...@good-stuff.co.uk wrote:
 Hi,

 I have a query like this:

 select id, title from product where id in (1,3,5,8,10)

 What I want it to do is return the rows in the order specified in the in
 clause, so that this:

 select * from product where id in (10,3,8,5,1)

 will give me results in this order:

 +--+-+
 | id   | title   |
 +--+-+
 |  10  |    foo  |
 +--+-+
 |   3  |    baz  |
 +--+-+
 |   8  |    bar  |
 +--+-+
 |   5  | wibble  |
 +--+-+
 |   1  | flirble |
 +--+-+

 Is this possible? If so, how?


select * from product where id in (10,3,8,5,1) order by field(id,10,3,8,5,1)

should do the trick...

Regards,

Joeri

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Order by in clause

2010-11-09 Thread Daevid Vincent 
 

 -Original Message-
 From: Joeri De Backer [mailto:fons...@gmail.com] 
 Sent: Tuesday, November 09, 2010 1:16 AM
 To: mysql
 Subject: Re: Order by in clause
 
 On Tue, Nov 9, 2010 at 10:09 AM, Mark Goodge 
 m...@good-stuff.co.uk wrote:
  Hi,
 
  I have a query like this:
 
  select id, title from product where id in (1,3,5,8,10)
 
  What I want it to do is return the rows in the order 
 specified in the in
  clause, so that this:
 
  select * from product where id in (10,3,8,5,1)
 
  will give me results in this order:
 
  +--+-+
  | id   | title   |
  +--+-+
  |  10  |    foo  |
  +--+-+
  |   3  |    baz  |
  +--+-+
  |   8  |    bar  |
  +--+-+
  |   5  | wibble  |
  +--+-+
  |   1  | flirble |
  +--+-+
 
  Is this possible? If so, how?
 
 
 select * from product where id in (10,3,8,5,1) order by 
 field(id,10,3,8,5,1)
 
 should do the trick...
 
 Regards,
 
 Joeri

...ya learn something new every day... ;-)

Here's more on this topic:
http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html 
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org