CODEWITHSUNDEEP
sqlpostgresqlsql-order-byrank
Filipe Braganca
Filipe Braganca

Reputation: 126

Order by One column But Push to the end if Another column is null

I'm trying to sort some results based on rank, but I want to push the results without an image to the end.

I have a table with instructor_profiles and another users table. I'm trying to sort the instructor's profile based on dense rank but would like to show the ones without pictures at the end.

My rank is working fine, but I can't figure it out how to do it.

For example:

SELECT ts_rank_cd("instructor_profiles"."search_vector", plainto_tsquery('english', 'skate')),
 "instructor_profiles".id, 
 "instructor_profiles".title, 
 "instructor_profiles".image, 
 users.avatar
 FROM "instructor_profiles" LEFT JOIN users ON users.id = instructor_profiles.user_id 
 WHERE ("instructor_profiles"."search_vector" @@ plainto_tsquery('english', 'skate')) 
 ORDER BY 
        ts_rank_cd("instructor_profiles"."search_vector", plainto_tsquery('english', 'skate')) desc
 LIMIT 50 OFFSET 0

Will get to this result: results

Then I tried to change the ORDER BY to

ORDER BY instructor_profiles.image NULLS LAST,
users.avatar NULLS LAST,
ts_rank_cd("instructor_profiles"."search_vector", plainto_tsquery('english', 'skate')) desc

But then my sort by the rank is totally messed up: results 2

Upvotes: 0

Views: 141

Answers (1)

MarcinJ
MarcinJ

Reputation: 3639

Use a CASE in your ordering, something like:

 ORDER BY CASE 
            WHEN instructor_profiles.image IS NOT NULL 
              OR users.avatar IS NOT NULL 
            THEN 0 
            ELSE 1  
          END ASC,
          ts_rank_cd DESC

This doesn't order by the image or avatar, just by the fact whether or not it is NULL, and all that aren't are treated equally and your ranking function will then apply.

Upvotes: 1

Related Questions