SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Order by

  1. #1
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Order by

    Hi,

    I have a simple tbl like so with 100's of records in it:

    id | name | order
    ----------------
    1 | Jon | 0
    2 | Jim |1
    3 | Fred | 0

    Now, I want to include all of these records in a query but I was wondering if there was any way I could order by asc starting from 1 rather than Zero?

    Thanks

  2. #2
    SitePoint Wizard bronze trophy Kailash Badu's Avatar
    Join Date
    Nov 2005
    Posts
    2,560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    SELECT * FROM tbl ORDER BY `order` ASC;

  3. #3
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, but that wll start the order at 0 though I want it to start at 1???

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    ORDER BY CASE WHEN order=0 THEN 937 END, order

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks!

  6. #6
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Out of interest if I then wanted to order by another two columns after this ie:

    Code:
    , published DESC , id DESC
    Code:
    SELECT *
    FROM `test_stories`
    ORDER BY CASE WHEN order =0
    THEN order =1, published DESC , id DESC

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by 7724 View Post
    Code:
    SELECT *
    FROM `test_stories`
    ORDER BY CASE WHEN order =0
    THEN order =1, published DESC , id DESC
    that's invalid syntax

    did you try mine?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •