Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Posts
    11

    Unanswered: My data get's Ordered by default!

    So my problem. I have an item which has specific attributes that the user can select from a list. Now the order of these attributes when being displayed is very important.

    To illustrate:
    Item: Jason
    Attribute1: Eats
    Attribute2: Food
    Attribute3: To
    Attribute4: Live

    Now that's the order the user selects so when I display the item with the attributes I want to see "Jason eats food to live"

    However the attributes were inserted into the database as follows
    Attribute Table
    ID | AttributeName
    1 Lives
    2 To
    3 Eat
    4 Food

    My query is
    SELECT AttributeName FROM Attributes WHERE AttributeId = 3 OR AttributeId = 4 OR AttributeId = 2 OR AttributeId = 1

    However the recordset returned is automatically ordered by ID. So it becomes "Jason lives to eat food"

    How can I prevent this?

    PS: Nevermind my grammatical changes to the data

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT AttributeName FROM Attributes WHERE AttributeId in ( 3, 4, 2, 1 )
    order
    by case when AttributeId = 3 then 0 else 1 end
    , case when AttributeId = 4 then 0 else 1 end
    , case when AttributeId = 2 then 0 else 1 end
    , case when AttributeId = 1 then 0 else 1 end
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2006
    Posts
    11
    Thanks a bunch Rudy, that rocked.

    If it's not too much to ask, what exactly does the then/else bit do?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you would like to see how it works, please run this query --
    Code:
    SELECT AttributeName
         , case when AttributeId = 3 then 0 else 1 end as sortkey1
         , case when AttributeId = 4 then 0 else 1 end as sortkey2
         , case when AttributeId = 2 then 0 else 1 end as sortkey3
         , case when AttributeId = 1 then 0 else 1 end as sortkey4
      FROM Attributes 
     WHERE AttributeId in ( 3, 4, 2, 1 )
    order 
        by case when AttributeId = 3 then 0 else 1 end
         , case when AttributeId = 4 then 0 else 1 end
         , case when AttributeId = 2 then 0 else 1 end
         , case when AttributeId = 1 then 0 else 1 end
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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