SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    Canada
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cannot sort by a renamed column?

    EDIT: I meant "aliased column", not "renamed column".

    Can someone explain to me why this doesn't work?

    Code:
    SELECT Col1, Col2 AS 'RenamedCol' FROM MyTable ORDER BY RenamedCol
    It refuses to obey the "ORDER BY" clause, but if I do this...

    Code:
    SELECT Col1, Col2 AS 'RenamedCol' FROM MyTable ORDER BY Col2;
    ...it sorts it as expected.

    I can't see reason why the first query shouldn't work.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    it does so work

    setup --
    Code:
    CREATE TABLE MyTable 
    ( Col1 INTEGER NOT NULL 
    , Col2 VARCHAR(99)
    );
    INSERT INTO MyTable VALUES
     (1,'one')
    ,(2,'two')
    ,(3,'buckle my shoe')
    ,(4,'four')
    ,(5,'shut de door')
    ; 
    SELECT Col1
         , Col2 AS 'RenamedCol' 
      FROM MyTable 
    ORDER 
        BY RenamedCol
    ;
    results --
    Code:
    Col1  RenamedCol
      3   buckle my shoe
      4   four
      1   one
      5   shut de door
      2   two
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    Canada
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah... problem solved, I wasn't using backticks.

    Move along, nothing to see here, folks...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by epp_b View Post
    I wasn't using backticks.
    neither was i

    'these are single quotes'

    `these are backticks`

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

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You shouldn't need to use backticks in any case. The only time you'd use them is if the name of the column or the alias name were reserved words and you wouldn't use those right? :-o

  6. #6
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    Canada
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    mysql> SELECT ItemID, CADMarkupPercent AS 'CAD m/u' FROM Items ORDER BY `CAD m/u` LIMIT 3;
    +--------+---------+
    | ItemID | CAD m/u |
    +--------+---------+
    |     35 |    0.00 |
    |     53 |   15.00 |
    |     52 |   15.00 |
    +--------+---------+
    3 rows in set (0.00 sec)
    
    mysql> SELECT ItemID, CADMarkupPercent AS 'CAD m/u' FROM Items ORDER BY 'CAD m/u' LIMIT 3;
    +--------+---------+
    | ItemID | CAD m/u |
    +--------+---------+
    |      8 |  100.00 |
    |     11 |   50.00 |
    |     12 |  100.00 |
    +--------+---------+
    3 rows in set (0.00 sec)
    It's not because of the wonky symbols in the alias either, I tried them without.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ORDER BY 'CAD m/u' is ineffective because you're sorting on a string ('CAD m/u')

    mysql lets you use single quotes to assign a column alias, because the syntax is unambiguous -- whatever comes after the AS is the alias

    but it does not let you use single quotes to refer to a column alias, because there's no way to distinguish that from a string

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

  8. #8
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    Canada
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup, thanks.


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
  •