code_junkie

Case statement in SQL Rownumber() Order By clause not working on varchar and int values

Nov 12th, 2011
63
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.59 KB | None | 0 0
  1. DECLARE @temp table
  2. (ShipNo int,
  3. Supplier varchar(10)
  4. )
  5.  
  6. INSERT INTO @temp VALUES (1,'CFA')
  7. INSERT INTO @temp VALUES (1, 'TFA')
  8. INSERT INTO @temp VALUES (2, 'LRA')
  9. INSERT INTO @temp VALUES (2, 'LRB')
  10. INSERT INTO @temp VALUES (3, 'ABC')
  11. INSERT INTO @temp VALUES (4, 'TFA')
  12.  
  13. Declare @OrderBy varchar(255)
  14. sET @OrderBy = 'Supplier'
  15.  
  16.  
  17.  
  18. SELECT ROW_NUMBER() OVER (ORDER BY
  19. CASE @OrderBy
  20. WHEN 'Supplier' THEN Supplier
  21. WHEN 'ShipNo' THEN ShipNo
  22. END
  23. ) AS RowNo,
  24. ShipNo,
  25. Supplier
  26. FROM @temp
  27.  
  28. CASE
  29. WHEN Boolean_expression THEN result_expression
  30. [ ...n ]
  31. [
  32. ELSE else_result_expression
  33. ]
  34. END
Add Comment
Please, Sign In to add comment