Order by "in" clause

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=gcdmg-mysql-2@m.gmane.org

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 =A0 | title =A0 |
> +------+---------+
> | =A010 =A0| =A0 =A0foo =A0|
> +------+---------+
> | =A0 3 =A0| =A0 =A0baz =A0|
> +------+---------+
> | =A0 8 =A0| =A0 =A0bar =A0|
> +------+---------+
> | =A0 5 =A0| wibble =A0|
> +------+---------+
> | =A0 1 =A0| 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=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Order by "in" clause

=20

> -----Original Message-----
> From: Joeri De Backer [mailto:fonsken@gmail.com]=20
> Sent: Tuesday, November 09, 2010 1:16 AM
> To: mysql
> Subject: Re: Order by "in" clause
>=20
> On Tue, Nov 9, 2010 at 10:09 AM, Mark Goodge=20
> 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=20
> 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 =A0 | title =A0 |
> > +------+---------+
> > | =A010 =A0| =A0 =A0foo =A0|
> > +------+---------+
> > | =A0 3 =A0| =A0 =A0baz =A0|
> > +------+---------+
> > | =A0 8 =A0| =A0 =A0bar =A0|
> > +------+---------+
> > | =A0 5 =A0| wibble =A0|
> > +------+---------+
> > | =A0 1 =A0| flirble |
> > +------+---------+
> >
> > Is this possible? If so, how?
> >
>=20
> select * from product where id in (10,3,8,5,1) order by=20
> field(id,10,3,8,5,1)
>=20
> should do the trick...
>=20
> Regards,
>=20
> 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=20
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html #function_fie=
ld


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg