SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Specific row on top of results

    Hello everyone,

    Someone knows how I could list all the records of a table with the very first result being of a specified value that exists in the table?

    For example :

    column 1
    a
    b
    c
    d

    Query result with c needed on top :

    c
    a
    b
    d


    Query result with b needed on top :

    b
    a
    c
    d


    Thanks

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select theColumn
      from theTable
    order
        by case when theColumn = 'b'
             then 0
             else 1
           end

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks longneck

    But how do I implement this when working with two tables?

    Here is my query :

    SELECT *
    FROM cats AS cats_tab, prods AS prods_tab
    WHERE prods_tab.prod_cat_ID = cats_tab.cat_ID
    ORDER BY cats_tab.cat_ID

    I want to make an arbitrary ordering on cat_ID

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ...when cats_tab.cat_ID = 'b'

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmm ... cat_ID has 3 records (1, 2 and 3) and the ordering works only when cat_ID = 2.

    When different from 2, the order gets totally weird.
    I don't get it

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ah, i didn't form a complete example because i was assuming that you already had a sort condition:
    Code:
    case when cats_tab.cat_ID = 'b'
      then 0
      else 1
    end,
    cats_tab.cat_ID


Bookmarks

Posting Permissions

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