Thursday, April 01, 2004

This post is really for my own future reference but it may help someone if they are as new to MySQL as I am. I am just completing a page that returns the results of a Rally competition, I needed to list the outright positions of the Driver championship as a table however the table lists all points by individual event and I needed to add up all the points for each driver across all events and return the recordset ordered by the least number of points. I knew I could use SUM(expr) to total the points but wasn't sure how to order by this new value which is when I came across 'AS'. The SQL statement is as follows:

SELECT *,SUM(OutrightPoints) AS position FROM table GROUP BY DriverID ORDER BY position

As you can see using 'AS' assigns the expression result to 'position' so you can use that for ORDER BY.

That one had me scratching my head for some time!

Jon 2:29 PM Permalink