n1concepts Posted May 5, 2013 Share Posted May 5, 2013 Hi, I need to sort a query 1st based on 'lb' column, then 'oz' column so that the results show - in DESC order from the 'paired' grouping of highest to lowest 'lb', 'oz' results. Here'st the full requests showing all the weights from userid 3: mysql> select lb,oz from tfish where userid = 3; +----+----+ | lb | oz | +----+----+ | 7 | 11 | | 11 | 15 | | 6 | 15 | | 14 | 11 | | 7 | 14 | | 7 | 16 | +----+----+ 6 rows in set Objective: I want to sort on the highest weights and show them, descending, which should list in this order: 14, 11 7, 16 7, 14 7, 11 ===== This is what I get on my query when attempting to order on highest 'lb' and in DESC with limit of 4: Note: giving you more queries as well so you see the tabled info unfiltered: mysql> select lb,oz from tfish where userid = 3 order by lb desc; +----+----+ | lb | oz | +----+----+ | 14 | 11 | | 11 | 15 | | 7 | 11 | | 7 | 14 | | 7 | 16 | | 6 | 15 | +----+----+ 6 rows in set mysql> select lb,oz from tfish where userid = 3 order by lb desc limit 4; +----+----+ | lb | oz | +----+----+ | 14 | 11 | | 11 | 15 | | 7 | 11 | | 7 | 14 | +----+----+ 4 rows in set mysql> Notice in the last query, 7, 16 is missing and 7,11 listed 1st (7,16 should be listed as the 1st '7lb' reference b/c of 'oz' being 16, followed by 7lb 14 which then excludes 7lb 11oz from the query results. Ok, (below) I'm providing the full table so you see everything - any help appreciated! (thx!) mysql> select id,userid,lb,oz from tfish; +----+--------+----+----+ | id | userid | lb | oz | +----+--------+----+----+ | 1 | 3 | 7 | 11 | | 2 | 3 | 11 | 15 | | 3 | 1 | 5 | 11 | | 4 | 3 | 6 | 15 | | 5 | 3 | 14 | 11 | | 6 | 3 | 7 | 14 | | 7 | 1 | 5 | 4 | | 8 | 3 | 7 | 16 | +----+--------+----+----+ 8 rows in set Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted May 5, 2013 Share Posted May 5, 2013 (edited) ORDER BY lb DESC, oz DESC LIMIT 4 Edited May 5, 2013 by mac_gyver Quote Link to comment Share on other sites More sharing options...
Solution n1concepts Posted May 6, 2013 Author Solution Share Posted May 6, 2013 Thanks - i knew I was missing something in the query syntax (works perfectly!) mysql> select lb,oz from tfish where userid = 3 order by lb desc, oz desc limit 4; +----+----+ | lb | oz | +----+----+ | 14 | 11 | | 11 | 15 | | 7 | 16 | | 7 | 14 | +----+----+ 4 rows in set Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.