SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: as... order by

  1. #1
    SitePoint Addict Raioo's Avatar
    Join Date
    Jul 2000
    Location
    Boston
    Posts
    212
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    as... order by

    Can we do something like:

    SELECT a, b, c, (a-b) as x FROM t WHERE i=3 ORDER BY x DESC

    What I want are a bunch of rows ordered by the difference between b and a in a descending order.

    Thanks.
    I appreciate the help of everyone at SitePoint.com

  2. #2
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can reference items in the select list using their position.

    E.g this:
    Code:
     SELECT
     	a
     ,	b
     ,	c
     FROM
     	t
     ORDER BY
     	b
     ,	c
     ,	a
     
     Means the same as:
     
     SELECT
      	a
      ,	b
      ,	c
      FROM
      	t
      ORDER BY
      	2
      ,	3
      ,	1
    So you want:
    Code:
     SELECT
     	a
     ,	b
     ,	c
     ,	(a-b) AS x
     FROM
     	t
     WHERE i = 3
     ORDER BY
     	4

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, but you are allowed to use the column alias in the ORDER BY (but not the WHERE clause)

    so you actually can do exactly like you wanted:

    SELECT a, b, c, (a-b) as x FROM t WHERE i=3 ORDER BY x DESC
    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
  •