List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 10 1999 12:37pm
Subject:Re: Order By Problem
View as plain text  
> At 12:10 PM -0500 8/10/99, Ping Lau wrote:
[...]
> >itemno char(10)
[...]
> >select * from poitem where po = "123" order by itemno
> >
> >But the result did not show item numbers in the correct order: 1, 10, 2, 3,
> >4, 5, 6, 7, 8, 9,1A!  How can I force item number 10 to appear after 1A?

'1A' is sorted as the last item?

I can't believe that, because sorting as strings should give:
  1, 10, 1A, 2, 3, 4, 5, 6, 7, 8, 9

If you want the char field "itemno" to be sorted numerical, try this:
  SELECT *, 0+itemno AS sortkey
  FROM   poitem
  WHERE  po = "123"
  ORDER BY sortkey, itemno
This will sort numerically, and "itemno"s with the same numerical
value "sortkey" will be sorted by their string value.

On Mo, 1999-08-09 23:28:15 -0500, Paul DuBois wrote:
> You're trying to sort strings as though they are numbers.
> 
> Why?

Good question!  Ping: Why?

If your "itemno"s really have to include letters, too (such not being
sheer numbers anymore), you're maybe better off padding them with
leading zeros, so that a normal string sort will work again:
  001
  001A
  002
  ...
  009
  010

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Order By ProblemPing Lau10 Aug
  • Re: Order By ProblemPaul DuBois10 Aug
    • Re: Order By ProblemMartin Ramsch10 Aug
Re: Order By ProblemMagnus Hammar10 Aug
  • Reply-To (was: Order By Problem)Martin Ramsch10 Aug
  • Re: Order By Problem(Paul D. Smith)10 Aug
Re: Order By ProblemPing Lau10 Aug
  • Re: Order By ProblemRaghu Machepalli10 Aug
  • Re: Order By ProblemChristian Mack10 Aug
Re: Order By ProblemPing Lau11 Aug