XtreamXTC
asked on
SQL ORDER BY Before GROUP BY
Hi,
I have a back end database with a list of players, their games played and won. When formatting this for displaying on my web page I want to order them by their percentage score (Won/Played).
However because this data is grouped before the ordering, its not sorting them correctly. I don't have anything in the table other than Name, Played, Won. All the other information I'm creating on the server.
Is there a way where I can:
1. Order by a variable thats not in the database (e.g. WinPer in the below code)
2. Order by before the Group by happend
All help much appreciated, example code attached.
Thanks.
I have a back end database with a list of players, their games played and won. When formatting this for displaying on my web page I want to order them by their percentage score (Won/Played).
However because this data is grouped before the ordering, its not sorting them correctly. I don't have anything in the table other than Name, Played, Won. All the other information I'm creating on the server.
Is there a way where I can:
1. Order by a variable thats not in the database (e.g. WinPer in the below code)
2. Order by before the Group by happend
All help much appreciated, example code attached.
Thanks.
sqllea = "SELECT tblPlayers.Name, tblPlayers.Player_ID, "
sqllea = sqllea & "SUM(tblResults.Played - tblResults.Won) AS Lost, "
sqllea = sqllea & "SUM(tblResults.Played) AS TotalPlayed, "
sqllea = sqllea & "SUM(tblResults.Won) AS TotalWon, "
sqllea = sqllea & "SUM(tblResults.Won / tblResults.Played *100) AS WinPer "
sqllea = sqllea & "FROM tblResults INNER JOIN tblPlayers ON tblResults.Player_ID = tblPlayers.Player_ID "
sqllea = sqllea & "GROUP BY tblResults.Player_ID, tblPlayers.Name, tblPlayers.Player_ID "
sqllea = sqllea & "ORDER BY SUM(tblResults.Won / tblResults.Played) DESC"
sqllea = "SELECT * from ( "
sqllea = sqllea & "SELECT tblPlayers.Name, tblPlayers.Player_ID, "
sqllea = sqllea & "SUM(tblResults.Played - tblResults.Won) AS Lost, "
sqllea = sqllea & "SUM(tblResults.Played) AS TotalPlayed, "
sqllea = sqllea & "SUM(tblResults.Won) AS TotalWon, "
sqllea = sqllea & "SUM(tblResults.Won / tblResults.Played *100) AS WinPer "
sqllea = sqllea & "FROM tblResults INNER JOIN tblPlayers ON tblResults.Player_ID = tblPlayers.Player_ID "
sqllea = sqllea & "GROUP BY tblResults.Player_ID, tblPlayers.Name, tblPlayers.Player_ID "
sqllea = sqllea & "ORDER BY SUM(tblResults.Won / tblResults.Played) DESC ) as Tbl order by WinPer"
ASKER
Thanks for the quick reply, although it still seems to be grouping before the ordering. Here is the page on the site so you can see what is happening:
http://www.pool.drunkensailor.co.uk/index.asp?pageid=1
the WinPer variable is what is used on the right hand side and what I want to Order By, as you can see its still not ordering correctly :(
http://www.pool.drunkensailor.co.uk/index.asp?pageid=1
the WinPer variable is what is used on the right hand side and what I want to Order By, as you can see its still not ordering correctly :(
try this
sqllea = "SELECT TotalWon/TotalPlayed * 100 as WinPer FROM "
sqllea = sqllea & "(SELECT tblPlayers.Name, tblPlayers.Player_ID, "
sqllea = sqllea & "SUM(tblResults.Played - tblResults.Won) AS Lost, "
sqllea = sqllea & "SUM(tblResults.Played) AS TotalPlayed, "
sqllea = sqllea & "SUM(tblResults.Won) AS TotalWon, "
sqllea = sqllea & "FROM tblResults INNER JOIN tblPlayers ON tblResults.Player_ID = tblPlayers.Player_ID "
sqllea = sqllea & "GROUP BY tblResults.Player_ID, tblPlayers.Name, tblPlayers.Player_ID) "
sqllea = sqllea & "ORDER BY TotalWon/TotalPlayed DESC"
Hey, ur ORDER BY clause is incorrect. Try this.
sqllea = "SELECT tblPlayers.Name, tblPlayers.Player_ID, "
sqllea = sqllea & "SUM(tblResults.Played - tblResults.Won) AS Lost, "
sqllea = sqllea & "SUM(tblResults.Played) AS TotalPlayed, "
sqllea = sqllea & "SUM(tblResults.Won) AS TotalWon, "
sqllea = sqllea & "SUM(tblResults.Won / tblResults.Played *100) AS WinPer "
sqllea = sqllea & "FROM tblResults INNER JOIN tblPlayers ON tblResults.Player_ID = tblPlayers.Player_ID "
sqllea = sqllea & "GROUP BY tblResults.Player_ID, tblPlayers.Name, tblPlayers.Player_ID "
sqllea = sqllea & "ORDER BY (SUM(tblResults.Won / tblResults.Played) * 100) DESC"
Tried this?
sqllea = "SELECT tblPlayers.Name, tblPlayers.Player_ID, "
sqllea = sqllea & "SUM(tblResults.Played - tblResults.Won) AS Lost, "
sqllea = sqllea & "SUM(tblResults.Played) AS TotalPlayed, "
sqllea = sqllea & "SUM(tblResults.Won) AS TotalWon, "
sqllea = sqllea & "SUM(tblResults.Won / tblResults.Played *100) AS WinPer "
sqllea = sqllea & "FROM tblResults INNER JOIN tblPlayers ON tblResults.Player_ID = tblPlayers.Player_ID "
sqllea = sqllea & "GROUP BY tblResults.Player_ID, tblPlayers.Name, tblPlayers.Player_ID "
sqllea = sqllea & "ORDER BY 6 DESC"
sqllea = "SELECT tblPlayers.Name, tblPlayers.Player_ID, "
sqllea = sqllea & "SUM(tblResults.Played - tblResults.Won) AS Lost, "
sqllea = sqllea & "SUM(tblResults.Played) AS TotalPlayed, "
sqllea = sqllea & "SUM(tblResults.Won) AS TotalWon, "
sqllea = sqllea & "SUM(tblResults.Won / tblResults.Played *100) AS WinPer "
sqllea = sqllea & "FROM tblResults INNER JOIN tblPlayers ON tblResults.Player_ID = tblPlayers.Player_ID "
sqllea = sqllea & "GROUP BY tblResults.Player_ID, tblPlayers.Name, tblPlayers.Player_ID "
sqllea = sqllea & "ORDER BY 6 DESC"
Scratch that, silly me :-|
ASKER
Thanks guys, but still no joy :(
ee_rlee - It doesn't like the syntax, also TotalWon and TotalPlayed are not within the database, they are created server side.
Viogi - Sorry mate, still does the same thing.
ee_rlee - It doesn't like the syntax, also TotalWon and TotalPlayed are not within the database, they are created server side.
Viogi - Sorry mate, still does the same thing.
here we go:
sqllea = "SELECT tblPlayers.Name, tblPlayers.Player_ID, "
sqllea = sqllea & "SUM(tblResults.Played - tblResults.Won) AS Lost, "
sqllea = sqllea & "SUM(tblResults.Played) AS TotalPlayed, "
sqllea = sqllea & "SUM(tblResults.Won) AS TotalWon, "
sqllea = sqllea & "SUM(tblResults.Won / tblResults.Played *100.0) AS WinPer "
sqllea = sqllea & "FROM tblResults INNER JOIN tblPlayers ON tblResults.Player_ID = tblPlayers.Player_ID "
sqllea = sqllea & "GROUP BY tblResults.Player_ID, tblPlayers.Name, tblPlayers.Player_ID "
sqllea = sqllea & "ORDER BY SUM( CAST(tblResults.Won as decimal(20,4)) / tblResults.Played * 100.0) DESC"
ps: how do you display the data in the grid? I assume that you have maybe a grid/dataview that does a sorting on Pld desc, overriding the SQL's order by.
run the query directly, and you will see that it will order by correctly
run the query directly, and you will see that it will order by correctly
ASKER
Hi Angelll,
I'm getting an error code "error '80004005' " with the above.
Its being displayed on a web page using ASP, nothing in there is overriding the SQL sort, so its just moving through each record after the SQL sortation.
The sortation is working to an extent, in that I know its using the SQL, I tried AVG and that seemed to work better, but was still not getting it right, because there is more than 1 record for each player.
I'm getting an error code "error '80004005' " with the above.
Its being displayed on a web page using ASP, nothing in there is overriding the SQL sort, so its just moving through each record after the SQL sortation.
The sortation is working to an extent, in that I know its using the SQL, I tried AVG and that seemed to work better, but was still not getting it right, because there is more than 1 record for each player.
When you run the query in a query analyzer or if its SQL 2005 in management studio do the results show correctly? I too think there it must be something overwriting the SQL default sort. That should work. Please let us know if you get the same messed up result running the query directly in the SQL Server and not in your control.
Thanks.
Thanks.
can you this? TotalWon and TotalPlayed should work because it is a field from a new table, t.
sqllea = "SELECT (TotalWon/TotalPlayed * 100) as WinPer FROM "
sqllea = sqllea & "(SELECT tblPlayers.Name, tblPlayers.Player_ID, "
sqllea = sqllea & "SUM(tblResults.Played - tblResults.Won) AS Lost, "
sqllea = sqllea & "SUM(tblResults.Played) AS TotalPlayed, "
sqllea = sqllea & "SUM(tblResults.Won) AS TotalWon, "
sqllea = sqllea & "FROM tblResults INNER JOIN tblPlayers ON tblResults.Player_ID = tblPlayers.Player_ID "
sqllea = sqllea & "GROUP BY tblResults.Player_ID, tblPlayers.Name, tblPlayers.Player_ID) t"
sqllea = sqllea & "ORDER BY (TotalWon/TotalPlayed) DESC"
ASKER
Hi Guys,
Thanks for all the support, I've attached the whole ASP code used in the web page, Its running from a back end Access Database, so I'm not using SQL Server, as I don't have it installed. :(
As you can see from the code, nothing is overriding the sort, and it works fine when sorting on other criteria, e.g. played or won. I think its trying to sort by the 1st record for each player, rather than whole of the won/played for each player.
Sorry if i've not been very discriptive, very much a newb at SQL.
Code.txt
Thanks for all the support, I've attached the whole ASP code used in the web page, Its running from a back end Access Database, so I'm not using SQL Server, as I don't have it installed. :(
As you can see from the code, nothing is overriding the sort, and it works fine when sorting on other criteria, e.g. played or won. I think its trying to sort by the 1st record for each player, rather than whole of the won/played for each player.
Sorry if i've not been very discriptive, very much a newb at SQL.
Code.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks ee_rlee that works a treat, all the time I was looking at it from the wrong angle!
Thanks to all those who helped!
Thanks to all those who helped!
Open in new window