Jump to content

Sort results based on two columns in specific order


Go to solution Solved by n1concepts,

Recommended Posts

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
  • Solution

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
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.