CODEWITHSUNDEEP
mysqlsql-order-by
user1029834
user1029834

Reputation: 783

Query order by specific number and after, do ordering by ascending

Table subject:

+--------+----+
|  name  | id |
+--------|----+
| apple  | 3  |
| banana | 1  |
| tree   | 4  |
| horse  | 6  |
| bird   | 7  |
| raq    | 2  |
+--------+----+

So I try to have an ouput like that (if, for example, my $id_subject = 4, my next row MUST be 5 -6 7-1....)

+--------+----+
|  name  | id |
+--------+----+
| tree   | 4  |
| horse  | 6  |
| bird   | 7  |
| banana | 1  |
| raq    | 2  |
| apple  | 3  |
+--------+----+

My query:

select subject_url,subjects.id
    from students_group
    left join teacher_group on teacher_group.group_school_id = students_group.group_id
    left join subjects on teacher_group.subject_id = subjects.id
    where   students_group.user_id = 83
    ORDER BY subjects.id = 5 desc

But I got this output:

+--------+----+
|  name  | id |
+--------+----+
| tree   | 4  |
| apple  | 3  |
| banana | 1  |
| horse  | 6  |
| bird   | 7  |
| raq    | 2  |
+--------+----+

How can I make it work as expected?

Upvotes: 1

Views: 249

Answers (3)

ajreal
ajreal

Reputation: 47331

ORDER BY 
if (subjects.id=$id_subject, -1,
  if (subjects.id>$id_subject, 0, 1)
), subjects.id

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

select subject_url,subjects.id
from students_group
left join teacher_group on teacher_group.group_school_id = students_group.group_id
left join subjects on teacher_group.subject_id = subjects.id
where   students_group.user_id = 83
ORDER BY 
     (subjects.id >= 4) DESC
   ,  subjects.id       ASC

Upvotes: 0

golimar
golimar

Reputation: 2548

Try

ORDER BY subjects.id >= 5 desc, subjects.id asc

"subjects.id >= 5" will return 1 or 0, so the rows for which is true will come first, and among those rows, they will be ordered ascending by id

Upvotes: 1

Related Questions