Mysql price and quantity selection

Question

There is a table with content:

+------+-------+----------+ | name | price | quantity | +------+-------+----------+ | alip | 10 | 0 | +------+-------+----------+ | mime | 200 | 0 | +------+-------+----------+ | vini | 70 | 1 | +------+-------+----------+ | simi | 30 | 10 | +------+-------+----------+

Need to sort in

 +------+-------+----------+ | name | price | quantity | +------+-------+----------+ | simi | 30 | 10 | +------+-------+----------+ | vini | 70 | 1 | +------+-------+----------+ | alip | 10 | 0 | +------+-------+----------+ | mime | 200 | 0 | +------+-------+----------+

That is, the sample is first sampled, if the field is less than one, then it will go to love after the field with the number greater than zero. And then all products must be sorted by price.

I do this:

 SELECT * FROM (SELECT * FROM `exp` ORDER BY `quantity` ASC) a ORDER BY `price` ASC

Or

 SELECT * FROM (SELECT * FROM `exp` WHERE quantity > 0 ORDER BY `price` ASC) a, (SELECT * FROM `exp` WHERE quantity < 1 ORDER BY `price` ASC) b

But of course everything goes wrong. I do not understand how to make the correct request.

Example: http://sqlfiddle.com/#!2/7ebf1/8

Em ... the service is temporarily unavailable in sqlfiddle, so I’ll write: what prevents you from sorting by two fields at once: SELECT * FROM exp ORDER BY quantity
If there are integers, then you can safely use my method, only DESC should be specified for the price.
I looked at your requests for something, and then ASC was there, although DESC

Yura Ivanov Yura Ivanov 25.2k one 22 52 · Accepted Answer · 2014-12-10T16:03:13

Zero quantities at the end, all for the price.

 select * from exp order by quantity<1, price

Yura Ivanov

25.2k one 22 52

one
As far as I understand, if the quantity is greater than one, then it must also be sorted by quantity. Just some kind of sophisticated wording)) - BOPOH
@BOPOH, how can I sort by two fields at the same time? ;) said:> And then all products must be sorted by price. - Yura Ivanov
@Yura Ivanov, you can sort by 10 fields at the same time) First, sorted by the first, if they are the same - then by the second field. If and then there are the same (i.e., the first and second are the same), then sort by the third. - BOPOH
@BOPOH, no, you already invented it. You read the conditions of the problem. clearly: those that are less than 1 should go after those with a quantity> 1 and all for the price. Now, if there something coincides somewhere - this is “later” ... and nothing is said about it. it will be necessary to be sorted later by adding quantity to the end of the order by list. - Yura Ivanov
Honestly, I didn’t quite understand his question either, so I put a question mark in my answer - Ale_x

|

BOPOH BOPOH 4,594 2 20 48 · Answer 2 · 2014-12-11T03:19:24

If you really need an option that pointed Comrade. @Yura Ivanov (i.e., “with zero number” - at the end, and “not with zero” not necessarily sorted by quantity), then it will be much better to use a slightly different approach:

create an additional field is_absent, which is dynamically updated (if the number is 0, then we set true here, you can update, for example, with triggers)
add this field to all indexes. If we do not need the sorting specified in the task, then we can add the WHERE is_absent IN (true, false) condition to the query WHERE is_absent IN (true, false)

Such a request will be processed much faster than using the ORDER BY quantity<1 , since in this case filesort will work. Even adding an index across the quantity field will not save it.

In addition, adding is_absent will help a lot when it’s necessary to remove missing items. Here again - WHERE quantity > 0 will complicate the query (even if there is an index), MySQL will not be able to use some indexes / optimizations, etc.

Those. in the end, your request will look like:

 SELECT * FROM exp ORDER BY is_absent, price

@lampa, I just typed a little something: >> If we don’t need the sorting specified in the task, then we can add a condition to the query with the sorting is not related.
Here was meant a little bit different: if you add this field to already existing indices, then it will be necessary to add either is_absent IN (true, false) to the condition or is_absent = true so that the rest of the index keys work.
In general, about indices (and MySQL) is worth reading, for example, here .
I haven’t read the third part yet, only the second is a bit (and it’s for 5.1 in total)
I do not say that this is an ideal solution, in some cases it is worth making differently, but in general, I see no reason to reject this solution.
In addition, this approach is just mentioned in the book I have cited, as one of the solutions to one of the tasks.
For educational purposes, it may be interesting and useful to know.
If, for example, the catalog is loaded every night, then with additional triggers / fields / indexes you will slow down the load and you may not have time to downtime.
You will have the file sort anyway, the sortings are changed by clicking on the column in the browser and you will not create indexes for all the options, but create them - see above.
This is the main reason for not inventing new entities unnecessarily.

Ale_x Ale_x 6.100 eight 15 · Answer 3 · 2014-12-10T15:45:16

 Select name, price, quantity FROM exp ORDER BY quantity DESC, price

So ?

Ale_x

6.100 eight 15

@Ale_x, Kakby @BOPOH has already talked about this ... Do not bother? - Deonis
@deonis, How would @BOPOH answer SELECT * FROM exp ORDER BY quantity ASC, price ASC - Ale_x
@Deonis, if I answered - the answer would be in the answers, I commented)) @Ale_x, First, yes - I did not read in what order I should sort. The second - again, yes, I confused what order should be sorted. But the third is already what is needed) - BOPOH

|

Mysql price and quantity selection

3 answers 3

More articles: