Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Unanswered: Ordering columns

    Consider the folowing:
    SELECT user.name, SUM(pts) as total_points FROM points INNER JOIN user ON (points.knytroID = user.id) GROUP BY knytroID ORDER BY total_points DESC;

    I would like to have a second column order by which 'knytroID' got the total_points value first. If I remove DESC from the statement, I preserve the order of which ID gets the points first because each entry in points is depentand upon time. However, when I add DESC, is seems to reverse the table before calculating SUM and changes the order.

    Using
    SELECT user.name, SUM(pts) as total_points FROM points INNER JOIN user ON (points.knytroID = user.id) GROUP BY knytroID ORDER BY total_points DESC, points.date ASC; does't work.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please make sure you understand all the changes i made...
    Code:
    SELECT user.name
         , SUM(pts) as total_points 
      FROM user 
    INNER 
      JOIN points 
        ON points.knytroID = user.id 
    GROUP 
        BY user.id 
    ORDER 
        BY total_points DESC
         , user.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2012
    Posts
    2
    I tried that and it works if the all the points are accumulated by one user.id before the other.
    Thank you

    EDIT:
    SELECT user.name
    , SUM(pts) as total_points
    FROM user
    INNER
    JOIN points
    ON points.knytroID = user.id
    GROUP
    BY user.id
    ORDER
    BY total_points DESC
    , user.date DESC

    works. Thank you for your help. Would you mind explaining it to me why changing the first select table makes this work?
    Last edited by Ionexchangeresin; 04-19-12 at 10:42.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Ionexchangeresin View Post
    Would you mind explaining it to me why changing the first select table makes this work?
    it wasn't changing the sequence of tables in the FROM clause that made it work

    in an INNER join you can write them in either sequence, i just changed it because it's more logical, as you have a one-to-many relationship and you are aggregating the many onto the one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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