In the database, I have a field as status which can have a value of

1, 2, 11, 12, 21, 22

1, 11, 21 Stands for processing

2, 12, 22 Stands for completed.

I am doing this query to get all data with sorting..

select se.*, cam.name, cam.exid, u.api_key, u.api_id from snapshot_extractors se
            left join cameras as cam on se.camera_id = cam.id
            left join users as u on cam.owner_id = u.id
            where lower(cam.name) like lower('%%') or lower(se.requestor) like lower('%%')
            order by se.status desc []

But when It sorts through status.. sorts works but in table formation where I am displaying the data, 22 and 2 means the same thing. but on sorting. either 2 will be up the list or 22 will be down or either way.

I am not getting any clue how I can make it work with sorting, so 22 and 2 can be at the top when I am sorting for completed ones.

  • Cast to text in sorting expression. order by se.status::text desc. Or maybe by last char then by value... – Akina Oct 15 at 13:56

You can sort using an expression:

order by case 
        when se.status in (2, 12, 22) then 1
        when se.status in (1, 11, 21) then 2
        else 3 -- just in case

This would sort rows with status "processing" before rows with "completed"

  • and where to put asc or desc? – Junaid Farooq Oct 15 at 13:57
  • @JunaidFarooq: after the END - but you don't need it as this already puts the rows processing first (you would only need DESC if you want those with "processing" at the end) – a_horse_with_no_name Oct 15 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.