Alex Alex - 4 months ago 8
MySQL Question

1 | 18.07 | 0mysql query sorting with different priority

i´ve been hitting rock bottom recently.

What i´m currently trying to do is "sorting" a selection of database entries by date and if the date has passed and value X has not changed then append it ontop of the list and if the value has changed at bottom.

Maybe its a bit better to make some sort of graphic explaining my problem.

Entry | "date" | X
1 | 10.07 | 0
1 | 11.07 | 1
1 | 12.07 | 1
1 | 13.07 | 1
1 | 14.07 | 1
1 | 15.07 | 1
1 | 16.07 | 1
1 | 17.07 | 1
1 | 18.07 | 0
1 | 19.07 | 1


This should transform to

Entry | "date" | X
1 | 19.07 | 1
1 | 17.07 | 1
1 | 16.07 | 1
1 | 15.07 | 1
1 | 14.07 | 1
1 | 13.07 | 1
1 | 12.07 | 1
1 | 11.07 | 1
1 | 18.07 | 0
1 | 10.07 | 0


Is that even possible to do with "pure" mysql ?
I´ve been trying to get it sort of working by "sorting" by date, but thats not what i wanted to accomplish in the end.

SELECT * FROM table_entries ORDER BY start_date ASC LIMIT 25


Have a great day

Answer

If I got it right you'd like to sort your rows by X value (having ones on top) and then sort them by date. You can achieve it by using
ORDER BY X,start_date
in your query.
You can specify ASC or DESC after X and start_date to obtain the desired effect
Your query wuld become something like this:
SELECT * FROM table_entries ORDER BY X DESC, start_date ASC LIMIT 25

Comments