tough view

[aspnet98]

I have no idea how to do the following and would appreciate help in some way

I have a view displaying the following:

ID Week Wins Difference name
1 1 1 2 jason
1 1 1 5 john
1 1 0 24 jay

I want to add a field that ranks the order. I sort wins desc and difference ascd to determine the order and winner.
I need to have a field that would rank the order 1 2 3 4 and so on.

Sometimes their may be a tie.

In that case the rank would do this 1, 1, 2, 3, 4, 5, 5, 6 etc...

Tie example:
ID Week Wins Difference name
1 1 1 2 jason
1 1 1 2 john


the logic is the most wins and smallest difference in the overall winner, and should follow this order.

is this possible?

 

[r937]

select ID 
     , Week 
     , Wins 
     , Difference 
     , name
     , ( select count(ID)+1 
           from xxxxxxx
          where Wins > X.Wins
             or Wins = X.Wins
            and Difference < X.Difference  ) 
         as position
  from xxxxxxx X
order by Wins desc, Difference asc

please note that, instead of this --

1, 1, 2, 3, 4, 5, 5, 6

the position will actually come out like this --

1, 1, 3, 4, 5, 6, 6, 8

if moe and larry tied for 1st, and curly came next, you don't say curly came 2nd, you say curly came 3rd

r937.com | rudy.ca

 

[aspnet98]

r937,
The view you suggested took way to long to run. Not feasable. Can you help me build it into the view I have now via a case statement?

Or could I somehow take the view I return in an asp page and do the ranking there.

I have no idea how to do either of my two suggestions.

 

[r937]

way too long? you gots any indexes defined on the table?

yes of course you can do the ranking in ASP

in fact, the front end (whether ASP or whatever) is the best place to do it

r937.com | rudy.ca

 

[aspnet98]

how in asp?

 

[r937]

no idea, i don't do asp

i assume it's like any other scripting language, you return a result set from the query, then "loop over" the result set one row at a time, check each new row for a control break in the win/difference columns, and increment a counter according...

r937.com | rudy.ca

 

[vongrunt]

Personally I often use something like this:

Dim oRS, iRank, iWins, iDiff
-- oCN is existing connection object
Set oRS = oCn.Execute("select somecolumn, ... from myTable order by Wins desc, Difference asc")
iRank = 1
Do While not oRS.Eof
	iWins = oRS("wins").value
	iDiff = oRS("Difference").value
	
	Do While not oRS.Eof
		If oRS("Difference").value<> iDiff or oRS("wins").value <> iWins Then Exit Do
		
		' use iRank/display record/do whatver you want here.
		
		oRS.moveNext
	Loop

	iRank = iRank + 1
Loop

This simple trick with nested loops may cover surprisingly many practical problems - grouping reports, ranking, even crosstabs. The only requirement is that given query must be properly ORDERed BY.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[aspnet98]

i get type mismatch on this line?

If oRS("Difference").value<> iDiff or oRS("wins").value <> iWins

 

[DotNetGnat]

try this:

If Cint(oRS("Difference").value)<> CInt(iDiff or oRS("wins").value) <> CInt(iWins)

or try using CDbl()

-DNG

 

[aspnet98]

that worked!
however it seems like it just hangs and returns nothing?

 

[DotNetGnat]

i think vongrunt just posted some sample code..did you use it as it is or did you tweak it to adapt to your needs...

are you getting any error??

-DNG

 

[DotNetGnat]

can you post your requirement again..i will see if i can come up with asp code to achieve what you want...

-DNG

 

[aspnet98]

ID Week Wins Difference name
1 1 1 2 jason
1 1 1 5 john
1 1 0 24 jay

I want to add a field that ranks the order. I sort wins desc and difference ascd to determine the order and winner.
I need to have a field that would rank the order 1 2 3 4 and so on.

Sometimes their may be a tie.

In that case the rank would do this 1, 1, 2, 3, 4, 5, 5, 6 etc...

Tie example:
ID Week Wins Difference name
1 1 1 2 jason
1 1 1 2 john

 

[aspnet98]

the view is called vwStandings.

 

[aspnet98]

any luck dotnet?