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 
>  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

Re: Order by "in" clause

2010-11-09 Thread Joeri De Backer 
On Tue, Nov 9, 2010 at 10:09 AM, Mark Goodge  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

Order by "in" clause

2010-11-09 Thread Mark Goodge 

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?

Mark
--
http://mark.goodge.co.uk

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