From: Peter Brawley
Date: October 30 2007 2:51pm
Subject: Re: Sort results by order in list
List-Archive: http://lists.mysql.com/mysql/209793
Message-Id: <472744FF.4040408@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------070100080309020000010609"

--------------070100080309020000010609
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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 helps.
>
> PaPa
>
> On 10/30/07, Peter Brawley <peter.brawley@stripped> 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
>>>
>>>
>>>       
>
>
>   

--------------070100080309020000010609--