Results 1 to 4 of 4

Thread: Custom sorting

  1. #1
    Join Date
    Apr 2006
    Posts
    9

    Unanswered: Custom sorting

    I was just wondering if there is something in postgres which allows me to custom sort some string columns. Let me explain what i mean.

    Suppose if there is a column named semester...which can contain legal values like spring, fall, and summer.

    Now if i want to sort this column in order fall, summer, spring.

    I know there can be many alternatives for this approach...
    But Is this possible ???

    Thanks,
    Sumeet.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by sumeetambre
    I was just wondering if there is something in postgres which allows me to custom sort some string columns. Let me explain what i mean.

    Suppose if there is a column named semester...which can contain legal values like spring, fall, and summer.

    Now if i want to sort this column in order fall, summer, spring.

    I know there can be many alternatives for this approach...
    But Is this possible ???

    Thanks,
    Sumeet.
    Add a lookup table to your database, holding a semester name, and a semester sort value (0-N)

    Join the tables in your queries, and sort by the sort value.

    Personally, I would probably put the numeric value in the base table, and add the text value in the lookup table. Then, join the description to the base table in the queries or Views.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    If you want to spare the lookup table (which would be the correct solution from a relation point of view) then you can use the case statement:
    Code:
    SELECT * 
    FROM theTable
    ORDER BY CASE 
              WHEN semester = 'spring' THEN 1
              WHEN semester = 'summer' THEN 2
              WHEN semester = 'fall' THEN 3
              WHEN semester = 'winter' THEN 4
        END

  4. #4
    Join Date
    Apr 2006
    Posts
    9

    Thanks a lot

    hey,

    thanks a lot i used shammats solution and it works. Thanks Again.

    Sumeet.

Posting Permissions

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