Re: [MySQL] Re: Reversing DESC|ASC

[Ashley M. Kirchner]

Jigal van Hemert wrote:
You can cheat a bit and use a UNION of one SELECT:
(SELECT field1,field2,field3 FROM table ORDER BY field3 ASC LIMIT 2) ORDER
BY field3 DESC;
   This didn't work as expected.  First, I need DESC sorting instead of 
ASC to get what I need:

   SELECT field1,field2,field3 FROM
       table ORDER BY field3 DESC LIMIT 2;
   ...gave me:
   +--------+------------------+---------------------+
   | field1 | field2           | field3              |
   +--------+------------------+---------------------+
   |   1037 | Thanksgiving Day | 2004-11-25 00:00:00 |
   |   1040 | Veteran's Day    | 2004-11-11 00:00:00 |
   +--------+------------------+---------------------+
   Good!  It's the two records I need.  Now to do the full query as you 
suggested:

   (SELECT field1,field2,field3 FROM
       table ORDER BY field3 DESC LIMIT 2)
       ORDER BY field3 ASC;
   ...something went wrong.  This now gives me:
   +--------+-----------------------+---------------------+
   | field1 | field2                | field3              |
   +--------+-----------------------+---------------------+
   |   1037 | Thanksgiving Day      | 2004-11-25 00:00:00 |
   |   1040 | Veteran's Day         | 2004-11-11 00:00:00 |
   |   1044 | Halloween             | 2004-10-31 00:00:00 |
   |   1045 | Daylight Savings Ends | 2004-10-31 00:00:00 |
   +--------+-----------------------+---------------------+
   How'd it go from 2 to 4 records, and they're also not reversed?  
Interesting to note: whether I do the final sorting DESC or ASC, it 
makes absolutely no difference to the order or quantity of records returned.


   Anyway, for the moment, I came up with a convoluted way of doing it:
   SELECT field1,field2,field3 FROM
       (SELECT field1,field2,field3 FROM table
       ORDER BY field3 DESC LIMIT 2)
   AS s1 ORDER BY field3 ASC;
   This will effectively give me:
   s1 => {5, 4} and {4, 5} as the final select (which is what I need.)  
Not sure why that works, but not your example.

--
H | I haven't lost my mind; it's backed up on tape somewhere.
 +--------------------------------------------------------------------
 Ashley M. Kirchner <mailto:[EMAIL PROTECTED]>   .   303.442.6410 x130
 IT Director / SysAdmin / WebSmith             .     800.441.3873 x130
 Photo Craft Imaging                       .     3550 Arapahoe Ave. #6
 http://www.pcraft.com ..... .  .    .       Boulder, CO 80303, U.S.A. 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]