List:General Discussion« Previous MessageNext Message »
From:Ben Clewett Date:April 8 2008 12:36pm
Subject:Re: Help with ORDER BY using two colomns
View as plain text  

I think the easiest is to create a new logical column with the correct 
ordering, something like:

SELECT *, IF(update != '', update, date) AS o
FROM my_table ORDER BY o DESC;

I note that both 'update' and 'date' are reserved works :)

Also worth noting that this cannot be assigned an index and is therefore 
only good for small amounts of data.

---------------------

If you have lots of data then you need a better solution.  Set 'update' 
to NULL where there is no value and add the key:

  KEY(update,date)

Then order by this key:

  ORDER BY update DESC, date DESC.

Which when I try it gives:

+-----+--------+----------+
| num | date   | update   |
+-----+--------+----------+
|   5 |     40 |       90 |
|   2 |     10 |       60 |
|   6 |     50 |     NULL |
|   4 |     30 |     NULL |
|   3 |     20 |     NULL |
|   1 |      1 |     NULL |
+-----+--------+----------+

Ben



Richard wrote:
> Hello,
> I've got a table which containes two date colomns.
> The first one is called `date` and the second `update`
> In the first one I put the ticket creation date, and on update I add or 
> change the update value.
> So the update colomn does not contain a value until the first update has 
> been done.
> I would like to order the tickets by their last update value. And if 
> this value does not exist use the date value.
> 
> at the moment I use this :
> 
> ORDER BY `date` DESC"
> and I would like to replace it by something like this :
> 
> ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE)
> 
> I know this code is completly wrong, just to try and show you what I 
> need ...
> 
> Here is an example of what I want to achieve
> 
> num    |    date    |    update
> -------------------------------------------
> 1    |    1    |   
> 2    |    10    |    60
> 3    |    20    |   
> 4    |    30    |   
> 5    |    40    |    90
> 6    |    50    |   
> 
> The required result would be :
> 
> num    |    date    |    update
> -------------------------------------------
> 5    |    40    |    90
> 2    |    10    |    60
> 6    |    50    |   
> 4    |    30    |   
> 3    |    20    |   
> 1    |    1    |   
> 
> Thanks in advance :)
> 

*************************************************************************
This e-mail is confidential and may be legally privileged. It is intended
solely for the use of the individual(s) to whom it is addressed. Any
content in this message is not necessarily a view or statement from Road
Tech Computer Systems Limited but is that of the individual sender. If
you are not the intended recipient, be advised that you have received
this e-mail in error and that any use, dissemination, forwarding,
printing, or copying of this e-mail is strictly prohibited. We use
reasonable endeavours to virus scan all e-mails leaving the company but
no warranty is given that this e-mail and any attachments are virus free.
You should undertake your own virus checking. The right to monitor e-mail
communications through our networks is reserved by us

  Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley,
  Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17
  Registered in England No: 02017435, Registered Address: Charter Court, 
  Midland Road, Hemel Hempstead,  Hertfordshire, HP2 5GE. 
*************************************************************************
Thread
Help with ORDER BY using two colomnsRichard8 Apr
  • Re: Help with ORDER BY using two colomnsKristian Myllymäki8 Apr
    • Re: Help with ORDER BY using two colomnsRichard8 Apr
      • Re: Help with ORDER BY using two colomnsRafael Barbolo Lopes8 Apr
        • Re: Help with ORDER BY using two colomnsRichard8 Apr
          • Re: Help with ORDER BY using two colomnsBen Clewett8 Apr
            • Re: Help with ORDER BY using two colomns [ solved thankyou :) ]Richard8 Apr
              • Re: Help with ORDER BY using two colomns [ solved thankyou :) ]Ben Clewett8 Apr
                • Re: Help with ORDER BY using two colomns [ solved thankyou :) ]Richard8 Apr
                  • Re: Help with ORDER BY using two colomns [ solved thankyou :) ]Andy Wallace8 Apr
                    • Re: Help with ORDER BY using two colomns [ solved thankyou :) ]Richard8 Apr
                  • Re: Help with ORDER BY using two colomns [ solved thankyou :) ]Ben Clewett8 Apr
      • Re: Help with ORDER BY using two colomnsTim McDaniel8 Apr
  • Re: Help with ORDER BY using two colomnsBen Clewett8 Apr