CODEWITHSUNDEEP
sqlamazon-redshift
redwolf_cr7
redwolf_cr7

Reputation: 2037

Redshift: row_number function over quantity in descending order, ranking values with 0 as rank 1

I have a dataset capturing the scores of students in various subjects. I am trying to rank the students based on their total score in descending order using the row_number() function in redshift. However, the dataset has few students with 0 scores in all of the subjects. The query is returning such students as rank 1 and then followed by the students in the correct order (descending). How do i ensure that the students with 0 score are ranked last?

Upvotes: 1

Views: 483

Answers (2)

naba
naba

Reputation: 181

I guess the issue is not with score being 0 rather score being NULL where NULL takes precedence over other values, you can re-write the query as

select . . .,
       row_number() over (order by coalesce(score,-1) desc) as rnk
;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269673

You can use case expressions in the row_number():

select . . .,
       row_number() over (order by (score > 0)::int desc, score)

If you want to leave them out of the ranking altogether:

select . . .,
       (case when score > 0
             then row_number() over (partition by (score > 0)::int order by score)
        end) as score_seqnum

Note: I suspect you really want rank() so students with the same score get the same ranking.

Upvotes: 1

Related Questions