Link to home
Start Free TrialLog in
Avatar of XtreamXTC
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.
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"

Open in new window

Avatar of sonicefu
sonicefu
Flag of Pakistan image


sqllea = "SELECT * from ( "
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"

Open in new window


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"

Open in new window

Avatar of XtreamXTC
XtreamXTC

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 :(
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"

Open in new window

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"

Open in new window

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"
Scratch that, silly me :-|
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.
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"

Open in new window

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
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.
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.
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"

Open in new window

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
ASKER CERTIFIED SOLUTION
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!