Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2010
    Posts
    5

    Unanswered: Select Query & Order by

    Experts

    Not sure if this is possible but I am looking for the following:

    I have a SQL statment like this


    PHP Code:

    SELECT PRIMARYINDEX 
    as UNIQUEID , * FROM MY_DATA_TABLE WHERE PRIMARYINDEX IN (47,48,49,50,68,69,70,72,73,81,85,90ORDER BY ORDER_FIELD 
    This will return the records ordered by the order field as expected

    for example the returnd records would be

    68

    73

    85

    48

    47

    72

    90

    ...etc

    What I need to to return the record of PRIMARYINDEX = 72 for example as first record and the remaining records ordered by the order field

    72

    73

    85

    48

    47

    90

    ...etc



    Is this possible?

    Any advice please?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    ORDER
        BY CASE WHEN primaryindex <> 72
                THEN 'humpty'
                ELSE 'dumpty' END
         , order_field
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Modify your ORDE BY clause:
    Code:
    SELECT...
    ORDER BY case PRIMARYINDEX when 72 then 0 else 1 end, ORDER_FIELD
    Last edited by rdjabarov; 03-23-10 at 13:11. Reason: Rudy beat me...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    0 and 1 are so pedestrian...

    the world would be a lot better with the occasional injection of some humour in our SQL

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ......................
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by n_i View Post
    ......................
    yes, humpty comes after dumpty

    gotcha!!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by r937 View Post

    gotcha!!!

    Yes, sometimes I type faster than I think.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by thewebhosting View Post
    Hi,

    You can find great example of select query with Order By at here:

    SELECT
    Select (SQL) - Wikipedia, the free encyclopedia
    oh please

    does either of those links explain how to put 72 first?

    didn't think so
    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
  •