doubt in sorting

• mehalingam nainar

  Newbie

  Points: 8

  More actions

  October 15, 2004 at 10:29 am

  #152296

  Dear steve,

  i am mega from india. i am a beginner to sql server.

  i have an urgent doubt to draw a query.

  i have a table with two fields,

  surveyno - int

  subdivno - varchar(10)

  data

  surveyno subdivno

  1 1

  1 1A

  1 2

  1 2A

  1 2B

  1 3

  upto

  1 20

  when i draw query like this

  "select surveyno,subdivno from table order by surveyno,subdivno"

  i get resultset as follows

  1 1A

  1 10

  1 11

  1 12

  1 13

  upto

  1 19

  1 2A

  1 2B

  1 3

  and so on

  but i want the resultset as follows

  1 1

  1 1A

  1 2

  1 2A

  1 2B

  1 3

  and so on

  could you pls please reply me as soon as possible

  this is most urgent

  thanks in advance

  my id is meha_stars@rediffmail.com

  yours

  mega

• Subodh-Rx

  SSC Rookie

  Points: 41

  More actions

  October 18, 2004 at 7:47 am

  #527025

  Hi Mega,

  Please check out this query...i think it will work

  drop table #temp

  create table #temp

  (

   surveyno  int,

   subdivno  varchar(10)

  )

  INSERT INTO #temp values(1, '1')

  INSERT INTO #temp values(3, '3B')

  INSERT INTO #temp values(2, '2')

  INSERT INTO #temp values(3, '3')

  INSERT INTO #temp values(2, '2B')

  INSERT INTO #temp values(3, '3A')

  INSERT INTO #temp values(2, '2A')

  INSERT INTO #temp values(1, '1A')

  select *, convert(int,convert(varchar(10),left(subdivno,len(subdivno)-1)) + convert(varchar(10),ascii(right(subdivno,1)))) as 'test'

  from #temp

  order by surveyno, test

  Subu.