www.webdeveloper.com
Results 1 to 3 of 3

Thread: using CASE for sorting

  1. #1
    Join Date
    Aug 2011
    Location
    Hong Kong
    Posts
    49

    Question using CASE for sorting

    i am using MYSQL and i would like to "SELECT' a table and sort according to a numerical value that i either get it from "column1" or "column2", so:

    ***if a number from column1 exist, then this number is used to be sorted, otherwise the number from column2 (which always exists) is the number used to be sorted***

    this is my current SQL statement:

    PHP Code:
    SELECT 
    FROM table 
    WHERE regtime 
    >=DATE_SUB(NOW(), INTERVAL 20 DAY)
    ORDER BY regtime ASC 
    i think i should use "CASE" but what i can do now is only sort in "column1" first if column1 IS NOT NULL, THEN sort in "column2"



    i appreciate your help as this is already one of my last stages on my personal website

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,612
    If it's a case of null or not null, you could do:
    Code:
    SELECT *, IFNULL(column_1, column_2) AS sort_by_this
    FROM table_name . . . ORDER BY sort_by_this
    Last edited by NogDog; 12-09-2012 at 06:37 PM.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Aug 2011
    Location
    Hong Kong
    Posts
    49
    awesome NogDog

    never know the "AS" can do the trick!

    thanks a million!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles