sql server: using variable order by in row_number()

Question

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

CASE - single value expression. You should use one CASE expression per each "virtual" column. — Roman Sergeev
– Roman Sergeev, Commented Sep 30, 2013 at 9:34

Jodrell · Accepted Answer · 2013-09-30 09:43:20Z

1

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

answered Sep 30, 2013 at 9:43

Jodrell

35.8k6 gold badges94 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

t-clausen.dk · Accepted Answer · 2013-09-30 10:32:20Z

1

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

edited Sep 30, 2013 at 10:32

answered Sep 30, 2013 at 9:41

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

2 Comments

Jodrell Over a year ago

as I recall, the - makes unexpected things happen with 0, just a vague hunch.

t-clausen.dk Over a year ago

@Jodrell no, but a - on datetime would cause problems, so you are half right

Damien_The_Unbeliever · Accepted Answer · 2013-09-30 09:41:13Z

0

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.

answered Sep 30, 2013 at 9:41

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

1 Comment

Damien_The_Unbeliever Over a year ago

@AshkanMobayenKhiabani - have you seen the updated version? asc and desc aren't part of specifying a value - so they can't be part of the CASE expression

Collectives™ on Stack Overflow

sql server: using variable order by in row_number()

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related