CODEWITHSUNDEEP
sql
Ashkan Mobayen Khiabani
Ashkan Mobayen Khiabani

Reputation: 34152

sql server: using variable order by in row_number()

I'm trying to use variable order by in row_number, it works when each case has a single column defined with no asc, or desc. but it doesn't work when I define more than one column or use asc or desc.

this works:

select top(@perpage) *,row_number()
 over (order by
case @orderby when 'updated' then date_edited
              when 'oldest' then date_added  
  end
) 
as rid from
(select * from ads) a
 where rid>=@start and rid<@start+@perpage

but I want to do this:

select top(@perpage) *,row_number()
 over (order by
case @orderby when 'updated' then date_edited, user_type desc
              when 'oldest' then date_added, user_type desc
              when 'newest' then date_added desc, user_type desc
              when 'mostexpensive' then price desc, user_type desc
              when 'cheapest' then pricedesc, user_type desc
              when '' then user_type desc
  end
) 
as rid from
(select * from ads) a
 where rid>=@start and rid<@start+@perpage

Upvotes: 0

Views: 2175

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44316

Try this instead:

select top(@perpage) *,row_number()
 over (order by
case @orderby when 'updated' then date_edited
              when 'oldest' then date_added
end,
case @orderby when 'newest' then date_added
end desc,
case @orderby when 'mostexpensive' then -price
              when 'cheapest' then pricedesc

end, 
  user_type desc
) 
as rid from ads a
where rid>=@start and rid<@start+@perpage

Upvotes: 1

Jodrell
Jodrell

Reputation: 35696

try this

select top(@perpage) *,row_number()
 over (order by
case @orderby when 'updated' then date_edited end,
case @orderby when 'oldest' then date_added end,
case @orderby when 'newest' then date_added end desc,
case @orderby when 'mostexpensive' then price end desc,
case @orderby when 'cheapest' then price end,
user_type desc
) 
as rid from
(select * from ads) a
 where rid>=@start and rid<@start+@perpage

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

CASE is an expression that returns a single value. Since the second sort column is always the same, then you can just have:

 over (order by
case @orderby when 'updated' then date_edited
              when 'oldest' then date_added
              when 'newest' then date_added desc
              when 'mostexpensive' then price desc
              when 'cheapest' then pricedesc
  end, user_type desc
)

(When @orderby is equal to '', then the case expression will produce NULL for every row - but since all NULLs sort together, this is fine)

Just noticed that not all of your columns names suggest compatible types - in which case this may be better:

 over (order by
case @orderby when 'updated' then date_edited
              when 'oldest' then date_added
  end,
  case @orderby 
        when 'newest' then date_added
  end desc,
  case @orderby
        when 'mostexpensive' then price
  end desc,
  case @orderby
        when 'cheapest' then price
  end asc,
  user_type desc
)

You can have a separate CASE statement for each data type and asc/desc combination needed - and as stated above, each one will just produce NULLs for any @orderby value not mentioned.

Upvotes: 0

Related Questions