Hello,

I have a query like:
SELECT id, start_date FROM iddt WHERE id IN ('109k7','s3x6','sxmns','wt57');
which gives me:
+-------+---------------------+
| id | start_date |
+-------+---------------------+
| 109k7 | 2007-10-07 12:06:58 |
| sxmns | 2007-10-06 02:17:30 |
| wt57 | 2007-10-07 15:57:37 |
| s3x6 | 2007-10-07 08:58:20 |
+-------+---------------------+

How can I get the results sorted by the order in which they appear in
the ID list? Indeed I could do it in the application but prefer to get
the results in the right order from MySQL as the ID list may be long
and I may need only first few entries cropped by LIMIT clause. Ideally
it should ORDER BY DATE(start_date) DESC and then by the ID list. Do I
need some sort of stored function for that?

I.e. the ideal output would be:
+-------+---------------------+
| id | start_date |
+-------+---------------------+
| 109k7 | 2007-10-07 12:06:58 |
| s3x6 | 2007-10-07 08:58:20 |
| wt57 | 2007-10-07 15:57:37 |
| sxmns | 2007-10-06 02:17:30 |
+-------+---------------------+

Thanks

PaPa

>I.e. the ideal output would be:
>+-------+---------------------+
>| id | start_date |
>+-------+---------------------+
>| 109k7 | 2007-10-07 12:06:58 |
>| s3x6 | 2007-10-07 08:58:20 |
>| wt57 | 2007-10-07 15:57:37 |
>| sxmns | 2007-10-06 02:17:30 |
>+-------+---------------------+

What rule generates the order 109k7, s3x6, wt57, sxmns?

PB

Papalagi Pakeha wrote:
> Hello,
>
> I have a query like:
> SELECT id, start_date FROM iddt WHERE id IN ('109k7','s3x6','sxmns','wt57');
> which gives me:
> +-------+---------------------+
> | id | start_date |
> +-------+---------------------+
> | 109k7 | 2007-10-07 12:06:58 |
> | sxmns | 2007-10-06 02:17:30 |
> | wt57 | 2007-10-07 15:57:37 |
> | s3x6 | 2007-10-07 08:58:20 |
> +-------+---------------------+
>
> How can I get the results sorted by the order in which they appear in
> the ID list? Indeed I could do it in the application but prefer to get
> the results in the right order from MySQL as the ID list may be long
> and I may need only first few entries cropped by LIMIT clause. Ideally
> it should ORDER BY DATE(start_date) DESC and then by the ID list. Do I
> need some sort of stored function for that?
>
> I.e. the ideal output would be:
> +-------+---------------------+
> | id | start_date |
> +-------+---------------------+
> | 109k7 | 2007-10-07 12:06:58 |
> | s3x6 | 2007-10-07 08:58:20 |
> | wt57 | 2007-10-07 15:57:37 |
> | sxmns | 2007-10-06 02:17:30 |
> +-------+---------------------+
>
> Thanks
>
> PaPa
>
>

Hi,

It comes from external source together with Relevance value (float),
where the first ID has highest relevance and subsequent IDs are in
decreasing order.

For example:
109k7 1.79
s3x6 1.34
sxmns 1.21
wt57 0.93

I could use these numbers in the query as well if it s.

PaPa

On 10/30/07, Peter Brawley <peter.brawley@earthlink.net> wrote:
> >I.e. the ideal output would be:
> >+-------+---------------------+
> >| id | start_date |
> >+-------+---------------------+
> >| 109k7 | 2007-10-07 12:06:58 |
> >| s3x6 | 2007-10-07 08:58:20 |
> >| wt57 | 2007-10-07 15:57:37 |
> >| sxmns | 2007-10-06 02:17:30 |
> >+-------+---------------------+
>
> What rule generates the order 109k7, s3x6, wt57, sxmns?
>
> PB
>
> Papalagi Pakeha wrote:
> > Hello,
> >
> > I have a query like:
> > SELECT id, start_date FROM iddt WHERE id IN ('109k7','s3x6','sxmns','wt57');
> > which gives me:
> > +-------+---------------------+
> > | id | start_date |
> > +-------+---------------------+
> > | 109k7 | 2007-10-07 12:06:58 |
> > | sxmns | 2007-10-06 02:17:30 |
> > | wt57 | 2007-10-07 15:57:37 |
> > | s3x6 | 2007-10-07 08:58:20 |
> > +-------+---------------------+
> >
> > How can I get the results sorted by the order in which they appear in
> > the ID list? Indeed I could do it in the application but prefer to get
> > the results in the right order from MySQL as the ID list may be long
> > and I may need only first few entries cropped by LIMIT clause. Ideally
> > it should ORDER BY DATE(start_date) DESC and then by the ID list. Do I
> > need some sort of stored function for that?
> >
> > I.e. the ideal output would be:
> > +-------+---------------------+
> > | id | start_date |
> > +-------+---------------------+
> > | 109k7 | 2007-10-07 12:06:58 |
> > | s3x6 | 2007-10-07 08:58:20 |
> > | wt57 | 2007-10-07 15:57:37 |
> > | sxmns | 2007-10-06 02:17:30 |
> > +-------+---------------------+
> >
> > Thanks
> >
> > PaPa
> >
> >
>

Papalagi Pakeha schrieb:
> Hello,
>
> I have a query like:
> SELECT id, start_date FROM iddt WHERE id IN ('109k7','s3x6','sxmns','wt57');
> which gives me:
> +-------+---------------------+
> | id | start_date |
> +-------+---------------------+
> | 109k7 | 2007-10-07 12:06:58 |
> | sxmns | 2007-10-06 02:17:30 |
> | wt57 | 2007-10-07 15:57:37 |
> | s3x6 | 2007-10-07 08:58:20 |
> +-------+---------------------+
>
> How can I get the results sorted by the order in which they appear in
> the ID list?

i do not fully understand ... ORDER BY `id` ... ???
what ID List?

> Indeed I could do it in the application but prefer to get
> the results in the right order from MySQL as the ID list may be long
> and I may need only first few entries cropped by LIMIT clause. Ideally
> it should ORDER BY DATE(start_date) DESC and then by the ID list. Do I
> need some sort of stored function for that?

ORDER BY DATE(`start_date`) DESC, `id`


> I.e. the ideal output would be:
> +-------+---------------------+
> | id | start_date |
> +-------+---------------------+
> | 109k7 | 2007-10-07 12:06:58 |
> | s3x6 | 2007-10-07 08:58:20 |
> | wt57 | 2007-10-07 15:57:37 |
> | sxmns | 2007-10-06 02:17:30 |
> +-------+---------------------+

On 10/30/07, Sebastian Mendel <lists@sebastianmendel.de> wrote:
> Papalagi Pakeha schrieb:
> > Hello,
> >
> > I have a query like:
> > SELECT id, start_date FROM iddt WHERE id IN ('109k7','s3x6','sxmns','wt57');
> > which gives me:
> > +-------+---------------------+
> > | id | start_date |
> > +-------+---------------------+
> > | 109k7 | 2007-10-07 12:06:58 |
> > | sxmns | 2007-10-06 02:17:30 |
> > | wt57 | 2007-10-07 15:57:37 |
> > | s3x6 | 2007-10-07 08:58:20 |
> > +-------+---------------------+
> >
> > How can I get the results sorted by the order in which they appear in
> > the ID list?
>
> i do not fully understand ... ORDER BY `id` ... ???
> what ID List?

This ID list: ('109k7','s3x6','sxmns','wt57')
I.e. I want to get the results in the same order in which they appear
in the above list. 109k7 first, s3x6 second, etc. Simple WHERE id IN
(...) gives me random order.

PaPa

Hi,
what you probably want is
SELECT id, start_date FROM iddt WHERE id IN
('109k7','s3x6','sxmns','wt57')
ORDER BY FIELD(id,'109k7','s3x6','sxmns','wt57')

/Johan

Papalagi Pakeha skrev:
> On 10/30/07, Sebastian Mendel <lists@sebastianmendel.de> wrote:
>> Papalagi Pakeha schrieb:
>>> Hello,
>>>
>>> I have a query like:
>>> SELECT id, start_date FROM iddt WHERE id IN ('109k7','s3x6','sxmns','wt57');
>>> which gives me:
>>> +-------+---------------------+
>>> | id | start_date |
>>> +-------+---------------------+
>>> | 109k7 | 2007-10-07 12:06:58 |
>>> | sxmns | 2007-10-06 02:17:30 |
>>> | wt57 | 2007-10-07 15:57:37 |
>>> | s3x6 | 2007-10-07 08:58:20 |
>>> +-------+---------------------+
>>>
>>> How can I get the results sorted by the order in which they appear in
>>> the ID list?
>> i do not fully understand ... ORDER BY `id` ... ???
>> what ID List?
>
> This ID list: ('109k7','s3x6','sxmns','wt57')
> I.e. I want to get the results in the same order in which they appear
> in the above list. 109k7 first, s3x6 second, etc. Simple WHERE id IN
> (...) gives me random order.
>
> PaPa
>

On 10/30/07, Johan Höök <johan.hook@pythagoras.se> wrote:
> Hi,
> what you probably want is
> SELECT id, start_date FROM iddt WHERE id IN
> ('109k7','s3x6','sxmns','wt57')
> ORDER BY FIELD(id,'109k7','s3x6','sxmns','wt57')

That's exactly it! Thanks a lot :-)

Just for the record, here's the FIELD() function description:
http://dev.mysql.com/doc/refman/5.0/...function_field

PaPa

PaPa,

>It comes from external source together with Relevance value (float),

Then you need to ORDER BY that func.

PB

-----

Papalagi Pakeha wrote:
> Hi,
>
> It comes from external source together with Relevance value (float),
> where the first ID has highest relevance and subsequent IDs are in
> decreasing order.
>
> For example:
> 109k7 1.79
> s3x6 1.34
> sxmns 1.21
> wt57 0.93
>
> I could use these numbers in the query as well if it s.
>
> PaPa
>
> On 10/30/07, Peter Brawley <peter.brawley@earthlink.net> wrote:
>
>>> I.e. the ideal output would be:
>>> +-------+---------------------+
>>> | id | start_date |
>>> +-------+---------------------+
>>> | 109k7 | 2007-10-07 12:06:58 |
>>> | s3x6 | 2007-10-07 08:58:20 |
>>> | wt57 | 2007-10-07 15:57:37 |
>>> | sxmns | 2007-10-06 02:17:30 |
>>> +-------+---------------------+
>>>
>> What rule generates the order 109k7, s3x6, wt57, sxmns?
>>
>> PB
>>
>> Papalagi Pakeha wrote:
>>
>>> Hello,
>>>
>>> I have a query like:
>>> SELECT id, start_date FROM iddt WHERE id IN ('109k7','s3x6','sxmns','wt57');
>>> which gives me:
>>> +-------+---------------------+
>>> | id | start_date |
>>> +-------+---------------------+
>>> | 109k7 | 2007-10-07 12:06:58 |
>>> | sxmns | 2007-10-06 02:17:30 |
>>> | wt57 | 2007-10-07 15:57:37 |
>>> | s3x6 | 2007-10-07 08:58:20 |
>>> +-------+---------------------+
>>>
>>> How can I get the results sorted by the order in which they appear in
>>> the ID list? Indeed I could do it in the application but prefer to get
>>> the results in the right order from MySQL as the ID list may be long
>>> and I may need only first few entries cropped by LIMIT clause. Ideally
>>> it should ORDER BY DATE(start_date) DESC and then by the ID list. Do I
>>> need some sort of stored function for that?
>>>
>>> I.e. the ideal output would be:
>>> +-------+---------------------+
>>> | id | start_date |
>>> +-------+---------------------+
>>> | 109k7 | 2007-10-07 12:06:58 |
>>> | s3x6 | 2007-10-07 08:58:20 |
>>> | wt57 | 2007-10-07 15:57:37 |
>>> | sxmns | 2007-10-06 02:17:30 |
>>> +-------+---------------------+
>>>
>>> Thanks
>>>
>>> PaPa
>>>
>>>
>>>
>
>
>