MySQL Forums
Forum List  »  Quality Assurance

MYSQL two column WHERE + SORT
Posted by: pavol m
Date: February 25, 2018 03:09PM

Hello everyone,
I have tried ask on stackoverflow and no one helped and I think solution must exist but we do not know how to do it.
https://stackoverflow.com/questions/48972215/mysql-two-table-sort-tricky

Please I would like to ask here, where are people more focused on MYSQL.

This is what I have in DB

date active
20180211 0
20180100 0
20171208 1

This is what I need show on page

date active
20171208 1
20180211 0
20180100 0

This working perfectly and active is on top of results and date is also sorted corectly
SELECT * FROM xy ORDER BY active DESC, date DESC

And now I need use WHERE and here is problem.... This "20171208" is shows as a last row but it must be first row because there is ACTIVE=1.

SELECT qry.* FROM
(SELECT * FROM XY ORDER BY active desc, date desc) qry
WHERE qry.date < 20171208
LIMIT 14;

http://sqlfiddle.com/#!9/5c37e7/29

if you remove WHERE outside ( )..... Result will be correctly sorted... But if you add this WHERE outside ( ) it is again not working properly... i dont know why... is maybee bug.. because in normal way they should show us all rows without first one "20171208" because we sorted it already inside ( ) ... but they again show as "20171208" as a last row in the table and they dont use ORDERED results inside ( )...

Please can you help me how to sort this my problem? Thank you very much Best regards Pavol

Options: ReplyQuote


Subject
Views
Written By
Posted
MYSQL two column WHERE + SORT
281
February 25, 2018 03:09PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.