ORDER BY complex question...

[shadedecho]

Let me premise all I am about to say (yes I know this is a loooong post) with the fact that I am running on MySQL 3.23.x, so I do not have the benefit of UNION or sub-selects or any of that other nice stuff to accomplish my objectives.

I would like to constuct a complex ORDER BY clause that will allow me to select descending "LIMIT"ed numbers of rows based on values in both columns... basically, in the following table, I'd like to select the 5 rows in descending order based on column A, but of those I want to pick out the 3 rows that have the greatest value in column B.

mytable:

+----+----+
| A  |  B |
+----+----+
| 1  |  1 |
| 2  | 99 |
| 3  | 16 |
| 4  | 85 |
| 5  | 46 |
| 6  | 19 |
| 7  | 18 |
| 8  |  0 |
| 9  | 45 |
| 10 | 23 |
+----+----+

Result *SHOULD* look like:

+----+----+
| A  |  B |
+----+----+
| 9  | 45 |
| 10 | 23 |
| 6  | 19 |
+----+----+

using something like

SELECT * FROM `mytable` ORDER BY A DESC, B DESC LIMIT 3

is what I originally thought... but notice the subtle difference here, that doing the 2 columns in the ORDER BY means it will only sort on "B" IF it finds multiple rows where "A" is the same... so it would actually just give:

+----+----+
| A  |  B |
+----+----+
| 10 | 23 |
| 9  | 45 |
| 8  |  0 |
+----+----+

So then I thought, "ORDER BY B DESC, A DESC"... but that produces:

+----+----+
| A  |  B |
+----+----+
| 2  | 99 |
| 4  | 85 |
| 5  | 46 |
+----+----+

My guess is that there is a way to sorta "dup" the behavior of the ORDER BY clause with a CASE (or an IF) statement, as is the case where CASE statement can allow you to define a custom order, instead of just using the ASC or DESC on that column...


(I have following 2 examples of complex ORDER BY queries which can achieve much more "useful" ORDER BY events than just a standard ASC or DESC... read FYI and to understand my ponder at whether a technique like this could be used to solve my original problem)

example 1:
say I wanted to order the results based on B, but give me the row where B = 19 first, then the row where B = 0 next, then the rest of them ordered by B descending:

SELECT * FROM `bob` ORDER BY (CASE `B` WHEN 19 THEN 0 WHEN 0 THEN 1 ELSE 2 END) ASC, `B` DESC

result:

+----+----+
| A  |  B |
+----+----+
| 6  | 19 |
| 8  |  0 |
| 2  | 99 |
| 4  | 85 |
| 5  | 46 |
| 9  | 45 |
| 10 | 23 |
| 7  | 18 |
| 3  | 16 |
| 1  |  1 |
+----+----+

Let me see if I can sorta explain what (I think) is happening here. Notice first the ORDER BY clause in this example is getting 2 "parts" to it... the first is the CASE statement, the second is the reference to ordering just on `B` descending. The first part is evaluated first, obviously. It checks to see if B = 19, and assigns any rows with that the case a "0", then it checks for B = 0, assigning those rows a "1", and assigns a "2" to every other row. It then applies the (default) ASC sort to those 0,1,2 values, and gets the 19-row first, the 0-row second, and everyone else after. However, since we have all the rest of the rows with a "2" assigned to them, for those rows, it applies the second "part" of the order, in this case just ordering on B descending.


example 2:
say I wanted to order the results based on B, but give me the rows where B is less than 40 in ASC order, first, and then the rows where B is greater than 40, in DESC order... this is how I would do that:

SELECT * FROM `bob` ORDER BY (IF (`B` < 40, 0,1)) ASC, (IF (`B` < 40, `B`,0)) ASC, `B` DESC

result set:

+----+----+
| A  |  B |
+----+----+
| 8  |  0 |
| 1  |  1 |
| 3  | 16 |
| 7  | 18 |
| 6  | 19 |
| 10 | 23 |
| 2  | 99 |
| 4  | 85 |
| 5  | 46 |
| 9  | 45 |
+----+----+

(takes deep breath)
Again, let me lend my explanation to how this works. Notice 3 parts to the ORDER BY clause, the 2 IF's, and the last `B` DESC. The first run through the result set, ORDER BY assigns a &quot;0&quot; to all the rows < 40, and a &quot;1&quot; to all rows greater than 40, then applies an ASC on the &quot;0&quot;,&quot;1&quot; values, splitting the list in half, with < 40 in the top half, and > 40 in the bottom half. THEN, for the rows with a &quot;0&quot; on them, it goes to the second IF, which tests for the same case of B < 40, and in that gives just B back, then applying the ASC to it, so the first half of the list is in ASC order. Then, the rows with a &quot;1&quot; assigned to them are run through that second IF statement, which gives them all a &quot;0&quot; since the B < 40 test would fail for the same rows that it failed for in the first test. Since the second half of the list still has &quot;0&quot; assigned to it, it goes to the third part of the ORDER BY, this time getting B DESC, and so it does that. viola!

 

[sleipnir214]

Here's one way (at least the first part of your question), using a temporary table and two queries:

CREATE TABLE tempfoo TYPE=HEAP SELECT * FROM minmax ORDER BY A DESC limit 5;
SELECT * FROM tempfoo ORDER BY b DESC LIMIT 3;

This works for me on 3.23.54 Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[swampBoogie]

Your problem can not be solved with one query. You need to create an intermediate result (order by a desc limit 5) in a temporary table and then use order by b desc limit 3 on that result.