#1
  1. Second highest poster :p
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jul 2001
    Posts
    7,322
    Rep Power
    33

    PostgreSQL Order By


    I have been having a problem when PostgreSQL was ordering some data via product name, the product names are as follows:

    1. ProductXYZ
    2. ProductHJF

    ...... and so on till

    10. Product GHT

    The client wanted the page to order them by the number, this worked but alphabetically 10 comes before 1. Due to the data field being varchar PostgreSQL ordered it alphabetically. Now our solution was to either modify the database and our pages that we had coded so far (quite a big task) or develop an SQL query that would rank it numerically. We decided there has to be a way to do this using SQL and we figured it out. If you have the same problem at any time use this.

    select * from products order by substring(product_name from 1 for position('.' in product_name)-1)::text::integer

    What this does is separate the number from the string, then type casts it as an integer and PostgreSQL sorts it perfectly.
    - Andreas Koepke

    Koepke Photography

  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    409
    Rep Power
    13

    Thumbs up


    Thanks for the tip Andreas, I'm sure I will run into that problem sooner or later.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2001
    Location
    Austin,TX
    Posts
    62
    Rep Power
    0
    very cool method! thanks!

IMN logo majestic logo threadwatch logo seochat tools logo