Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    41

    Angry Unanswered: Better sorting problem

    How can I make the code understand that when sorting something by size for example, that 8" in small than 10"?

    When I use:

    rs.Open "SELECT * FROM plant_list WHERE grp_name = '" & request.QueryString("group") & "' and available = True ORDER BY name, pot_size", conn

    I sorts it as 10" before 8". I am assuming this is because "1" comes before "8"?

    How can I work around it without using a leading zero?

    Thanks for any help.

  2. #2
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    did u try adding asc to the end?

    ORDER BY name, pot_size asc", conn
    Beyond Limitation

  3. #3
    Join Date
    Dec 2003
    Posts
    454
    If you do not add "ASC" the results will be sorted by ascent order. Since there are two fields in your "ORDER BY" clause, the first sort will be based on the field "name" and then "pot_size".

  4. #4
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    i had a similar sorting problem wiht my asp page.

    for me - i fixed it by changing the sroting column fortm 1,2,3, to 01,02,03
    Beyond Limitation

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108

    Re: Better sorting problem

    Originally posted by mturner
    How can I make the code understand that when sorting something by size for example, that 8" in small than 10"?

    When I use:

    rs.Open "SELECT * FROM plant_list WHERE grp_name = '" & request.QueryString("group") & "' and available = True ORDER BY name, pot_size", conn

    I sorts it as 10" before 8". I am assuming this is because "1" comes before "8"?

    How can I work around it without using a leading zero?

    Thanks for any help.
    I assume POT_SIZE is holding your 10 and 8 values.

    If Pot_size is a char or varchar, then 10 will always come first.

    If Pot_size is a numeric field of some sort, then 8 will always come first.

    `Le

  6. #6
    Join Date
    Feb 2004
    Posts
    41
    pot_size is holding the values 8" and 10" so it is varchar.

    It has to hold the " (for inches) or the ' (for feet). I cannot store just the number itself without the " or '.

    Any ideas? Thanks for the help.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108

    Arrow

    There is not much you can do here I am afraid. I would suggest you convert it to integer when you do the Select, but that will not help since you store Feet AND inches in the same field.

    For example, you could remove the ' and '', but that means 10'5'' would convert to 105, which makes no sense.

    If the field ONLY kept inches or ONLY kept feet, then it would work. But as such, there is no way I can think of to get the values you need.

    Sorry. This is just another reason we should be in the metric system.

    Ultimately the best solution is to covert all the data to inches and store it is a numeric or integer.

    Sorry, but it looks like you are out of luck on this one.

    `Le

  8. #8
    Join Date
    Feb 2004
    Posts
    41
    thele,

    I appreciate your feedback. It gave me a couple of ideas. I am going to recommend converting all sizes to inches, and storing just the integer in the db and adding the " to the display.

    Or I might go through alot of work, and store the " and ' in a separate field, thus stoing the integer by itself, and joining them later in the display. Of course I am hoping the client picks the former. (wishful thinking!)

    Thanks for your comments!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •