Order query by id and column

i have a table where shows the jobs offers, and im querying by latest jobs and the recommend jobs first, but its not working.



All the job ads that are marked has recommend are "1" and the ones are not "0", but for some reason the column recommend isnt Ordering.



The datatype of recommend column is int.



Here is my query:



SELECT * 

FROM jobs

WHERE active =  '1'

ORDER BY id_job DESC , recommend DESC


Answers

As it is now, it'll order by the job id first, and only by the recommend field if there are duplicates. I assume those ids are unique, which would mean the second "order by" part is essentially never used.



Try just swapping that.



SELECT * 

FROM jobs

WHERE active =  '1'

ORDER BY recommend DESC, id_job DESC


Answers

When you order by more than one columns, the ordering is done on the first column, and then the second column is used to break ties among rows where the first column is identical; the third column is used for tie breaking when values in the first and the second column are identical, and so on.



In your case it means that the ordering is on id_job, which is probably unique, so there are no ties to break.



Changing the order of columns should help:



SELECT * 

FROM jobs

WHERE active =  '1'

ORDER BY  recommend DESC  , id_job DESC