sorting issue

• Ahmad Osama

  SSCertifiable

  Points: 7676

  More actions

  June 29, 2010 at 1:53 am

  #99252

  I have a column num nvarchar(30) ... with values of the form 'ABC-0000001' which were to incremented automatically. The code I wrote works correctly until the value reaches to 'ABC-10000000'.

  Am using ..

  select top 1 @num=num from table order by num desc

  always gives 'ABC-9999999' also max(num) gives the same...

  any ideas

  Regards,
  [font="Verdana"]Sqlfrenzy[/font]

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 29, 2010 at 2:08 am

  #1186217

  Try sorting using the numeric part only:

  select top 1 @num = num from table order by right(num,len(num)-4) desc

  -- Gianluca Sartori

• Ahmad Osama

  SSCertifiable

  Points: 7676

  More actions

  June 29, 2010 at 2:42 am

  #1186237

  Gianluca Sartori (6/29/2010)

  ---

  Try sorting using the numeric part only:

  works with little modification

  select top 1 @num = num from table order by cast(right(num,len(num)-4) as int) desc

  Regards,
  [font="Verdana"]Sqlfrenzy[/font]

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 29, 2010 at 4:01 am

  #1186267

  Ooops! You're right, sorry for the bad advice.

  -- Gianluca Sartori