sql Inner Join

[gop373]

I have two tables. The first table name register.
id
Player_name
Email

Second table name showrank
id
Totalgainloss
CashAV

I want to show the Player_name who get the maximum Totalgainloss and also sho the number 0f totalgainloss.
I tried

sql="SELECT max(totalgainloss)as cashAV showrank.*, register.* FROM showrank INNER JOIN register ON showrank.id = register.id;"
set HighPrice=server.createobject("adodb.recordset")
HighPrice.open sql,conn,1,3

if not (HighPrice.eof) then
response.write HighPrice("cashAV")
response.write HighPrice("Player_name")
end if

But it did not work !! :(

[candelbc]

Maybe this will work.. This assumes that there is only one record in the showrank table to match the one record in the register table. It would be different if I was coding a ONE->MANY relationship.

SELECT register.Player_Name, showrank.totalGainLoss
FROM register INNER JOIN showrank ON register.id = showrank.id
ORDER BY showrank.totalGainLoss DESC;

-Brad

[russell]

SELECT Top 1 PlayerName, Max(Totalgainloss) as highGainLoss
FROM register r
INNER JOIN showrank s
On s.id = r.id
GROUP BY PlayerName
ORDER by Max(Totalgainloss) DESC