Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    3

    Unanswered: How to order a column

    To order a recordset I can simply do
    "Select * From Table Order by ColumnName;"

    But my need is different.

    In a column I have the following content:
    "C"
    "P"
    "F"
    "A"

    But "F" and "A" may exist more than once.

    I must order the recordset always in the order
    "C"
    "P"
    "F"
    "A"

    and not "A", "C", "F", "P".

    Can anyone pls help me?

    Thanks in advance.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    I'd add a column to your query (maybe named ordercol?) and populate it with a case statement based on your desired sortorder, then use this column in your order by clause.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sounds like homework...

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Col1 char(1))
    GO
    
    INSERT INTO myTable99(Col1)
    SELECT 'A' UNION ALL
    SELECT 'C' UNION ALL
    SELECT 'F' UNION ALL
    SELECT 'P' UNION ALL
    SELECT 'A' UNION ALL
    SELECT 'C' UNION ALL
    SELECT 'F' UNION ALL
    SELECT 'P' UNION ALL
    SELECT 'X' UNION ALL
    SELECT 'Z'
    GO
    
    SELECT Col1 FROM (
    SELECT DISTINCT Col1 FROM myTable99) AS XXX
    ORDER BY CASE Col1
    		WHEN 'C' THEN 1
    		WHEN 'P' THEN 2
    		WHEN 'F' THEN 3
    		WHEN 'A' THEN 4
    			 ELSE 9
    	 END
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think for maximum efficiency and to avoid hard-coding you should have a lookup table that lists the values with a column defining the sort order you want.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well without any more info, that thing is gonna scan anyway

    But I do like the idea of a table driven order system....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Always try to keep the hard-coding to a minimum...

    Plus, don't you think an index scan would be faster than a set of umpteen boolean comparisons applied to every record?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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