Re: Reversing DESC|ASC
You seem to be confused in your posting. Your first list is in ASC order and appears correct. Your second list seems to be in DESC order and also seems to be correct. Why is it your results need to have '2004-11-20' pretend to be AFTER '2004-11-24' so that it appears first in a descending order list? If I understood what you are trying to accomplish better, I could help you make your query appear as you like. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ashley M. Kirchner [EMAIL PROTECTED] wrote on 11/26/2004 03:10:41 AM: I'm currently running a query on a db that looks as follows: SELECT field1,field2,field3 FROM table ORDER BY field3 DESC LIMIT 5; This produces: ++-++ | field1 | field2 | field3 | ++-++ | 1 | Title 1 | 2004-09-08 | | 2 | Title 2 | 2004-10-23 | | 3 | Title 3 | 2004-11-11 | | 4 | Title 4 | 2004-11-20 | | 5 | Title 5 | 2004-11-24 | ++-++ My problem is, I need the last two in that list, in the order they're listed there. If I reverse the order (by using ASC), I will get: ++-++ | field1 | field2 | field3 | ++-++ | 5 | Title 5 | 2004-11-24 | | 4 | Title 4 | 2004-11-20 | | 3 | Title 3 | 2004-11-11 | | 2 | Title 2 | 2004-10-23 | | 1 | Title 1 | 2004-09-08 | ++-++ ...which puts the two that I need at the top, but not in the order I need them (I need 20th listed before the 24th). So, how do I reverse DESC sorting, to get the records in the order that I need? -- 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]
Reversing DESC|ASC
I'm currently running a query on a db that looks as follows: SELECT field1,field2,field3 FROM table ORDER BY field3 DESC LIMIT 5; This produces: ++-++ | field1 | field2 | field3 | ++-++ | 1 | Title 1 | 2004-09-08 | | 2 | Title 2 | 2004-10-23 | | 3 | Title 3 | 2004-11-11 | | 4 | Title 4 | 2004-11-20 | | 5 | Title 5 | 2004-11-24 | ++-++ My problem is, I need the last two in that list, in the order they're listed there. If I reverse the order (by using ASC), I will get: ++-++ | field1 | field2 | field3 | ++-++ | 5 | Title 5 | 2004-11-24 | | 4 | Title 4 | 2004-11-20 | | 3 | Title 3 | 2004-11-11 | | 2 | Title 2 | 2004-10-23 | | 1 | Title 1 | 2004-09-08 | ++-++ ...which puts the two that I need at the top, but not in the order I need them (I need 20th listed before the 24th). So, how do I reverse DESC sorting, to get the records in the order that I need? -- 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]
Re: Reversing DESC|ASC
- Original Message - From: Ashley M. Kirchner [EMAIL PROTECTED] SELECT field1,field2,field3 FROM table ORDER BY field3 DESC LIMIT 5; My problem is, I need the last two in that list, in the order they're listed there. If I reverse the order (by using ASC), I will get: So, how do I reverse DESC sorting, to get the records in the order that I need? 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; Usually you would use (SELECT ...) UNION (SELECT...) ORDER BY... to sort the result of the combined queries. MySQL seems to accept the use of only one SELECT with an implied UNION. I'm not certain how future versions of MySQL will respond to this variation. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MySQL] Re: Reversing DESC|ASC
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]
Re: [MySQL] Re: Reversing DESC|ASC
From: 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. Very odd! These are my results on MySQL 4.0.21 using InnoDB tables (it was suitable data for this test, InnoDB was used for other reasons): SELECT `msg_id` , `from_accountid` , `sent` FROM `msg_content` ORDER BY `sent` DESC LIMIT 5 msg_id from_accountid sent 48469 30328 2004-11-24 03:14:49 48468 16788 2004-11-24 03:06:47 48467 22935 2004-11-24 02:49:49 48466 18115 2004-11-24 02:46:45 48465 27356 2004-11-24 02:29:23 (SELECT `msg_id` , `from_accountid` , `sent` FROM `msg_content` ORDER BY `sent` DESC LIMIT 5 ) ORDER BY `sent` ASC msg_id from_accountid sent 48465 27356 2004-11-24 02:29:23 48466 18115 2004-11-24 02:46:45 48467 22935 2004-11-24 02:49:49 48468 16788 2004-11-24 03:06:47 48469 30328 2004-11-24 03:14:49 This works exactly as expected Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MySQL] Re: Reversing DESC|ASC
Jigal van Hemert wrote: These are my results on MySQL 4.0.21 using InnoDB tables (it was suitable data for this test, InnoDB was used for other reasons): rpm -qa | grep -i mysql MySQL-server-4.1.7-0 MySQL-devel-4.1.7-0 MySQL-client-4.1.7-0 MySQL-shared-compat-4.1.7-0 MySQL-Max-4.1.7-0 And it's a MyISAM table. This works exactly as expected No matter how I performed your query, it always came back with more records than expected, almost like it's ignoring the LIMIT value. With my query it works fine. I wonder if it's a feature of the newer version. -- 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]
