ranking based on sorting 1

[jacob94]

I have the following data in a table:
name wins diff pct pts
admin14 10 0 41 77
admin11 10 1 42 777
admin12 10 24 41 4443
administrator 10 24 62 50

I have the following view (check out the sorting).
Select * from tbltest order by wins desc, diff, pct desc, pts desc

The diff column needs ascending sort because lower is better for that sort all other need desc sort because higher is better.

I need to rank these with a numeric number based on the way they are sorted: 1 2 3 4

If they tie, I need to duplicate the rank. 1, 2, 2, 4 etc…

if there were only two columns the ranking would look like this:
name wins diff rank
admin14 10 0 1
admin11 10 1 2
admin12 10 24 3
administrator 10 24 3


I do not know how to write the ranking code in sql off this view. I also do not know how to figure the rank for all the columns. Can you provide some feed back on what i am trying to do.

 

[jacob94]

BTW I am on sql 2000 not 2005 as I know there are some new ranking tools in there...

 

[hmckillop]

I tried a temp variable like this

create table Table_TestOnly
( Field1 varchar(10), Field2 int,  Field3 int)

insert into Table_TestOnly values('admin14', 10,    0)
insert into Table_TestOnly values('admin11',     10,    1)     
insert into Table_TestOnly values('admin12',     10 ,   24)     
insert into Table_TestOnly values('administr',     10,    24)

select * from Table_TestOnly

declare @tmpTable TABLE (field1 varchar(10), field2 int, field3 int, rankfield int)

insert into @tmpTable (field1, field2, field3)
select * from Table_TestOnly order by field3 asc

declare @v_i int, @v_LastField3Value int

SELECT @v_LastField3Value = 0, @v_i = 1
UPDATE @tmpTable
SET Rankfield = @v_i,
	@v_i = CASE WHEN @v_LastField3Value <> field3 THEN @v_i + 1 else @v_i END,
	@v_LastField3Value = field3

select * from @tmpTable

My table represents your view, obviously you should replace this.
Probably better ways of doing it though.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[jacob94]

hmckillop thanks for the quick response.

will it work for all columns like the first example, if so how?

any way to do it with out temp table and right from my view?

 

[jacob94]

I was thinking something like this.
select *,

(Select Count(*)
from del1 R2
where R2.name = R1.name


and ( R2.wins >= R1.wins
and R2.diff < R1.diff )



) + 1 as Rank

from del1 R1
order by wins desc,diff

I am not sure how to get it to work for all the columns though?

 

[hmckillop]

The option you have will run like a quite slow if you have a large amount of rows.
If you want to use the sample I put for multiple rows, its relatively easy enough.

create table Table_TestOnly
( Field1 varchar(10), wins int,  diff int, pct int, pts int)

insert into Table_TestOnly values('admin14', 10,    0, 41, 77)
insert into Table_TestOnly values('admin11',     10,    1, 42, 777)     
insert into Table_TestOnly values('admin12',     10 ,   24, 41, 4443)     
insert into Table_TestOnly values('administr',     10,    24, 62, 50)
insert into Table_TestOnly values('admin14a', 10,    0, 41, 77)
insert into Table_TestOnly values('admin11a',     10,    1, 42, 777)     
insert into Table_TestOnly values('administra',     10,    24, 62, 50)

select * from Table_TestOnly

declare @tmpTable TABLE (Field1 varchar(10), wins int,  diff int, pct int, pts int, rankfield int)

insert into @tmpTable (Field1 , wins ,  diff , pct , pts )
select * from Table_TestOnly order by wins desc,  diff desc, pct desc

declare @v_i int, @v_LastWinsValue int, @v_LastDiffValue int, @v_LastPCTValue int 

SELECT @v_LastWinsValue = 0, @v_i = 0, @v_LastDiffValue = 0, @v_LastPCTValue = 0

UPDATE @tmpTable
SET Rankfield = @v_i,
    @v_i = CASE WHEN @v_LastWinsValue <> wins OR @v_LastDiffValue <> diff  OR @v_LastPCTValue <> PCT 
		THEN @v_i + 1 else @v_i END,
    @v_LastWinsValue = wins,
	@v_LastDiffValue = diff, 
	@v_LastPCTValue = pct


select * from @tmpTable

"I'm living so far beyond my income that we may almost be said to be living apart

 

[jacob94]

Ok I got your results. But diff needs to be ascd sorting. The names with diff = 0 should rank higher?

 

[jacob94]

also the ranking needs to work this way:
if there are 7 members in the table the last rank should be 7.

1
1
2
3
3
4
4

should be

1
1
3
3
5
5
7

 

[jacob94]

If you can help me with last post, I think this may work. Let me know.

 

[hmckillop]

To rank the names with diff = 0 higher change this statement to be the order you wish

select * from Table_TestOnly order by wins desc,  diff desc, pct desc

If you require to jump ranks when you have matches ie joint places are same rank with then for each joint place you skip an increment this is dont slightly differently - e.g.

/*create table Table_TestOnly
( Field1 varchar(10), wins int,  diff int, pct int, pts int)

insert into Table_TestOnly values('admin14', 10,    0, 41, 77)
insert into Table_TestOnly values('admin11',     10,    1, 42, 777)     
insert into Table_TestOnly values('admin12',     10 ,   24, 41, 4443)     
insert into Table_TestOnly values('administr',     10,    24, 62, 50)
insert into Table_TestOnly values('admin14a', 10,    0, 41, 77)
insert into Table_TestOnly values('admin11a',     10,    1, 42, 777)     
insert into Table_TestOnly values('administra',     10,    24, 62, 50)

select * from Table_TestOnly*/

declare @tmpTable TABLE (Field1 varchar(10), wins int,  diff int, pct int, pts int, rankfield int)

insert into @tmpTable (Field1 , wins ,  diff , pct , pts )
select * from Table_TestOnly order by wins desc,  diff desc, pct desc

declare @v_iRankIncrement int, @v_LastWinsValue int, @v_LastDiffValue int, @v_LastPCTValue int , @v_iCurrentRank int

SELECT @v_LastWinsValue = 0, @v_iRankIncrement = 0, @v_LastDiffValue = 0, @v_LastPCTValue = 0, @v_iCurrentRank = 0

UPDATE @tmpTable
SET Rankfield = @v_iCurrentRank,
    @v_iCurrentRank = CASE WHEN @v_LastWinsValue <> wins OR @v_LastDiffValue <> diff  OR @v_LastPCTValue <> PCT 
        THEN @v_iRankIncrement + 1 else @v_iRankIncrement END,
	@v_iRankIncrement = @v_iRankIncrement + 1,
    @v_LastWinsValue = wins,
    @v_LastDiffValue = diff, 
    @v_LastPCTValue = pct


select * from @tmpTable

Hope this helps

"I'm living so far beyond my income that we may almost be said to be living apart

 

[jacob94]

This code is awesome. Don't know how you do it???

One last thing. Does this line
insert into @tmpTable (Field1 , wins , diff , pct , pts )
select * from Table_TestOnly order by wins desc, diff desc, pct desc

drive the ranking?

I have another table of user configs in which they select from 4 sort options. So they could change the sort order.

How do you recommend feeding in those variables to this code that would dynamically change the sort/ranking.

other table
configs
name sort1 sort2 sort3 sort4
admin wins diff etc................

 

[hmckillop]

You could use a parameter in the stored proc to indicate which sort order you wanted then use a case statement.
As you need multiple fields sorted, it gets more complicated, and you need dynamic SQL and a fixed table (rather than a table variable).
If you give me the exact 4 sort orders I can post back with the result.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[jacob94]

hmckillop, thanks so much for the help on this, it had be a huge learning experience of what you can do with sql server.

I did some work on my end setting up a view that will provide all the information you need. With many complex joins I was able to link the 6 orderby fields so you know the way it needs to be sorted (hope this makes your part easier!)

the view is called vwFinal and I had to change some column names (hope that does not mess this up).

GroupID UserID Week Username PSWins PSTBDifference PSWinPercentage PSPoints_Thursday PSPoints_Saturday PSPointsSunday PSPointsMonday PSPoints_Total OrderBy1 OrderBy2 OrderBy3 OrderBy4 OrderBy5 OrderBy6

For each GroupID there are several UserIDs and Several Weeks. Every GroupID will have the same 6 order by options, they are unique to the GroupID. The only field that needs ascending sort is the PSTBDifference field. The OrderBy field would be populated with 6 of the seven columns with data (PSWins PSTBDifference PSWinPercentage PSPoints_Thursday PSPoints_Saturday PSPointsSunday PSPointsMonday PSPoints_Total). OrderBy1 should be sorted first etc....

I would love for it to be a stored procedure that writes this output to a table with all the appropriate fields of vwFinal. I would schedule this to run a few times a day.

Let me know if you need anything else.

THANKS!!!

 

[kaht]

jacob94, does your boss need hmckillop's address so he knows where to send your paycheck to?

-kaht

[small]How spicy would you like your chang sauce? Oh man... I have no idea what's goin' on right now...[/small]

 

[jacob94]

Not for my boss, for me...This has been a eye opener to me, I am just getting into SQL server and realize the potential it offers.

 

[johnwm]

jacob94,
It's amazing that you've asked so many questions, and got so many GOOD answers, but you haven't yet marked ANY as valuable. Try reading faq183-874 which gives guidance on forum usage, and what you should expect to give to the forum, and get from the forum

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[jacob94]

Ok, Thanks.

 

[hmckillop]

Whole different ball game from the original question and obviously slightly more complicated.
I would guess I would question the design of this but wont go into this now.
Firstly I would need to know do you pass in a group id and week field to the stored proc to then get the results back for this ranked on the columns specified in the order by statement?
It is non-sensical if you dont filter on group as this obviously means different sort orders .
If you confirm this I can then post again.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[jacob94]

Yes, I pass a group id and week. The group ids will all have the same sorting options and that is what the rank should be based on.

GroupID UserID Week Options - >
1000 1 1 same options
1000 2 1 same options
1002 1 1 diff options
1002 2 1 diff options

There can be multiple weeks within the groupid and they need to be ranked according to the groupid and week parameter.

 

[hmckillop]

Jacob

Big problems with this. You plan to have a dynamic rank order, this causes problems in the way I had structured things.

From a performance point of view you would be better of defining explicit set of ranking ways and then having a a large IF statement for each option.

i.e.
Instead of fields orderby1, orderby2, etc... you store a single field e.g. ORDERBYID
This is then retrieved and stored in a variable which is based on the groupid and week which allows you to then use
this variable for your case statement

IF @v_OrderByID= 1 THEN
BEGIN
...do the code I had shown in example within here using a different ORDER by Clause for each different orderbyID
END

This although more code, is probably the easiest way of doing what you require.
Dynamic SQL and physical temp tables all have performance implications.
Sticking to table variables and SQL statements which can be properly cached and indexed is much better,.

I personally would re-design your Database, but then this is just my opinion.


"I'm living so far beyond my income that we may almost be said to be living apart