0

I have the below query where I am trying to arrange the "weekNumber" in ascending order such as week1,week2,week3,week4,... and so on

SELECT WeekNumber,pointsRewarded FROM chart_copy 
WHERE EmployeeID = '2164'
order by WeekNumber asc ;

But the problem is it is not giving me the desired output.

What it gives is this :

img

Kindly help.I am using sql server -2012.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
JAne
  • 97
  • 1
  • 10
  • 1
    That sort is correct - you hand it a bunch of *strings* and it sorts the strings alphabetically. If you wish to consider sorting based on *some part* of that string consisting of *digits* and you'd like those to be sorted by *numeric* sorting rules, you might want to consider not *embedding* the numbers inside a string in the first place. Why are you *storing* the redundant string `week` in your `WeekNumber` column at all? Surely that text could be rendered by your front-end application or report builder? – Damien_The_Unbeliever Feb 14 '17 at 09:11
  • Since you already know that its the "week" number being stored, would suggest just storing the number as a numeric type, and removing the "week" prefix. That way, the sorting will yield the results that you need. – kkaosninja Feb 14 '17 at 09:14
  • whatever u guys are saying is obvious.Anyways,thanks for the input. – JAne Feb 14 '17 at 09:19
  • 3
    order by convert(int,replace(weekNumebr,'week','')) – Pramod Maharjan Feb 14 '17 at 09:21
  • @PramodMaharjan thanks.Works perfect. – JAne Feb 14 '17 at 09:33

0 Answers0