SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Alias in ORDER BY clause PROBLEM

    Hi everybody,

    I have the following sql query:

    Code:
    SELECT rp.service, rp.food, rp.value, rp.overall, rp.recommended, po.*, (rp.service + rp.food + rp.value + rp.overall) as RATING, COUNT(rp.id_rating) as VOTES
    FROM wp_ratepress rp
    INNER JOIN wp_comments co ON co.comment_ID = rp.id_comment 
    INNER JOIN wp_posts po ON po.ID = co.comment_post_ID
    GROUP BY rp.id_comment
    ORDER BY VOTES * RATING  DESC
    And I get Unknown column VOTES in ORDER BY clause.
    However if I do:
    Code:
    SELECT rp.service, rp.food, rp.value, rp.overall, rp.recommended, po.*, (rp.service + rp.food + rp.value + rp.overall) as RATING, COUNT(rp.id_rating) as VOTES
    FROM wp_ratepress rp
    INNER JOIN wp_comments co ON co.comment_ID = rp.id_comment 
    INNER JOIN wp_posts po ON po.ID = co.comment_post_ID
    GROUP BY rp.id_comment
    ORDER BY RATING  DESC
    or

    Code:
    SELECT rp.service, rp.food, rp.value, rp.overall, rp.recommended, po.*, (rp.service + rp.food + rp.value + rp.overall) as RATING, COUNT(rp.id_rating) as VOTES
    FROM wp_ratepress rp
    INNER JOIN wp_comments co ON co.comment_ID = rp.id_comment 
    INNER JOIN wp_posts po ON po.ID = co.comment_post_ID
    GROUP BY rp.id_comment
    ORDER BY VOTES DESC
    IT works!
    I think it doesn't like the alias in expressions like a * b or concat('-', b) with a or b being ALIAS.

    I have tried with _two_ mysql 5.0.x versions, so I don't think it's a bug or something.

    Can you help me out ?
    This problem is killing me!

    Thanks!

  2. #2
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Simply select a field that does your math, and then order by that alias.


    Code:
    SELECT
    (VOTES * RATING) AS MYORDER
    
    .
    .
    .
    
    ORDER BY MYORDER DESC


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
  •