Places - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Places

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-07-29 : 21:49:41

Will writes "I am trying to get 1st 2nd 3rd 4th etc places for a D-Base Result
I currently have say Wins Losses and Matches.
I am able to sort them in order using this querry:

SELECT (wins * 100 / matches) AS [win_perc] ,(wins) AS [win] ,(losses) AS [lose] ,(Callsign) AS [Name] FROM preferences WHERE (Game = '::Game::' AND Callsign <> '::Callsign::') ORDER BY Wins DESC,Losses ASC

Which also gives me their win %
But i need it to count where it placed them in the order it placed them. i have tried to add the count(field_name) but i get an error
is there an easier way to go about counting where they show?

Her is example of how it looks now

Mike
Wins Loses Win%
2 0 100%
Nick
Wins Loses Win%
0 2 0%

I would like it to put a 1 for mike and a 2 for nick and so on and say mike lost 4 matches to Nick which would put nick in mikes place and mike in nicks being they are the only people in the results.
Can you guys help?"

fyrye
Starting Member

4 Posts

Posted - 2003-02-07 : 20:22:13

Ok after my experience as a scripter I have found the easiest way to do this.
As well add st to 1 ie 1st
or nd to 2 ie 2nd all the way upto 999
Its about 100% faster then create , insert into, select, drop.
I hope this helps anyone who is trying to do similar


 
<%
'------------------>Placement Script/Function(Page 1)<----------------------
'directory/file is  "include/place.asp"
'placement 1st 2nd 3rd etc upto 999 with ability to loop
'define place

if uplace <> "" then
Mplace = Uplace
end if
if mid(Mplace,1,1) <> "" and mid(Mplace,2,1) = "" then
	if Mplace = "1" then
		Mplace2 = Mplace & "st"
		ELSEIF Mplace = "2" then
		Mplace2 = Mplace & "nd"
		ELSEIF Mplace = "3" then
		Mplace2 = Mplace & "rd"
		ELSE Mplace2 = Mplace & "th"
	END IF
ELSEIF mid(Mplace,1,1) <> "" AND left(Mplace,1) <> "1" AND mid(Mplace,2,1) <> ""  AND mid(Mplace,3,1) = "" then
	if mid(Mplace,2,1) = "0" then
		Mplace2 = Mplace & "th"
		ELSEIF mid(Mplace,2.1) = "1" then
		Mplace2 = Mplace & "st"
		ELSEIF mid(Mplace,2,1) = "2" then
		Mplace2 = Mplace & "nd"
		ELSEIF mid(Mplace,2,1) = "3" then
		Mplace2 = Mplace & "rd"
		ELSE Mplace2 = Mplace & "th"
	END IF
ELSEIF mid(Mplace,1,1) <> "" AND mid(Mplace,2,1) <> "1" AND mid(Mplace,2,1) <> "" AND mid(Mplace,3,1) <> "" then
	if mid(Mplace,3,1) = "0" then
		Mplace2 = Mplace & "th"
		ELSEIF mid(Mplace,3,1) = "1" then
		Mplace2 = Mplace & "st"
		ELSEIF mid(Mplace,3,1) = "2" then
		Mplace2 = Mplace & "nd"
		ELSEIF mid(Mplace,3,1) = "3" then
		Mplace2 = Mplace & "rd"
		ELSE Mplace2 = Mplace & "th"
	END IF
ELSE
Mplace2 = Mplace & "th"
END IF
%>
<%
'------------------>Placement Page (Page 2)<----------------------
%>
<%'View Results
mySQL = "SELECT * From Users order by Points DESC, WINS DESC, Losses ASC"
do while not rstemp.eof
username = rstemp("user")
l = l + 1
mplace = l
%>

<!--#include file="include/place.asp"-->


<%=Mplace%> <%=UserName%><br>
<%
rstemp.movenext
loop
%>

To see this in action http://tornsoft.com/warzone/WarZone_member_standings_page.asp

Edited by - fyrye on 02/07/2003 20:25:32

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources