dimanche 25 septembre 2016

Mysql variable equals if statement output

I need to order the records where variable equals another variable last. But it's not that simple. My ORDER BY:

ORDER BY (price_final = IF(count(u.store_id) > 0 AND count(u.store_id) <= 1,
(SELECT IFNULL(MAX(store.price_final), 0)
FROM books_used_state b_, store
WHERE b_.store_id = store.id
AND store.book_info_id = books_info.id),
(SELECT IFNULL(MIN(store.price_final), 0)
FROM books_used_state b_, store
WHERE b_.store_id = store.id
AND store.book_info_id = books_info.id))) ASC

Don't really break your brain looking at what it selects etc. Basically it has to check if price_final equals to one of the selects, depending on the if statement it should compare price_final to one select or the other. If I put one of the selects instead of the if it orders some of the records last, so it works in ordering by equality of price_final and a select. Basically with the if it probably orders by the select somehow and not the equality. This works but I need to compare to the other select sometimes also hence why I need the if to be working:

ORDER BY (price_final = (SELECT IFNULL(MAX(store.price_final), 0)
FROM books_used_state b_, store
WHERE b_.store_id = store.id
AND store.book_info_id = books_info.id)) ASC

Selecting the comparison in the start of the query and outputting it shows that values of price_final and the if statement output (one of the selects) are equal where they have to be and is_equal = 1, but in order it doesn't order by the equality with the if. Thanks for the help.

Aucun commentaire:

Enregistrer un commentaire