Ranking Records - 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

Old Forums

CLOSED - General SQL Server

Ranking Records

Author

Topic

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-07-20 : 16:40:21

I have searched the board but can't find an example for my need of ranking records.

In am trying to test this in Query Analyzer.

Basically I have four fields in tbl_Roster
Date,
SelectionYear,
YG,
LastName

I want a new field in my results called rank.

The ranking is done by ordering the records by Date, Selection Year, YG and lastly LastName

This is what I have but its coming back with all Ranks as 1
DECLARE @RelativeRank INT
DECLARE @n INT
SET @n = 0
SET @RelativeRank = (@n + 1)

Select
@RelativeRank AS Rank,
Date,
SelectionYear,
YG,
LastName
FROM tbl_Roster
ORDER BY DateOfRank, SelectionYear, YG, LastName

The results should be like this..

Rank Date SelectionYear YG LastName
1 10/20/1980 1975 2000.5 Wilkinson
2 9/16/1984 1975 2001.2 Smith
3 9/16/1984 1976 2001.2 Jenkins
4 12/15/1986 1972 2001.9 Frank

Any help would be most appreciated..

chadmat
The Chadinator

1974 Posts

Posted - 2004-07-20 : 17:14:27

Of course they are all coming back as 1, there is nothing happening to increment the @n or @RelativeRank variables. Try this KB Article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;186133&Product=sql

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-07-22 : 10:51:58

I tried this as the article suggest but get an error:

Select RelRank=count(*),
DateOfRank,
SelectionYear,
YG,
LastName
From tbl_Roster
ORDER BY DateOfRank, SelectionYear, YG, LastName

Example in the Article is:

select rank=count(*), a1.au_lname, a1.au_fname
from authors a1, authors a2
where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
group by a1.au_lname, a1.au_fname
order by 1

This is the error I am getting in Query Analyzer:

Server: Msg 8118, Level 16, State 1, Line 1
Column 'tbl_Roster.DateOfRank' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'tbl_Roster.SelectionYear' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'tbl_Roster.YG' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'tbl_Roster.LastName' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-22 : 11:13:16

You've missed the GROUP BY from your code. The bit in red in the example below

select rank=count(*), a1.au_lname, a1.au_fname
from authors a1, authors a2
where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
group by a1.au_lname, a1.au_fname
order by 1

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-07-22 : 11:19:40

Ok now it is working but the RelRank is not actually ranking the records. The field is 1 for all records.

Here is what I have.

Select RelRank=count(*),
DateOfRank,
SelectionYear,
YG,
LastName
From tbl_Roster
Where Rank_Code = 'c'
Group BY DateOfRank, SelectionYear, YG, LastName
Order By 1

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-22 : 11:21:35

When you rank on more than 1 column, it becomes more difficult, but here is how you do it:


select B.*,
  (Select count(*) from YourTable A
   where (A.Col1 < B.Col1) OR
         (A.Col1 = B.Col1 AND A.Col2 < B.Col2) OR
         (A.Col1 = B.Col1 AND A.Col2 = B.Col2 AND A.Col3 < B.Col3) OR
         (A.Col1 = B.Col1 AND A.Col2 = B.Col2 AND A.Col3 = B.Col3 AND A.Col4 < B.Col4)
  )+1 as Rank      
from
  Yourtable B

walk through it, it should make sense.

The above ranks sorted by Col1 ASC, Col2 ASC, Col3 ASC, Col4 ASC.

To sort by DESC, change the < to > for any given column comparison.

Try to apply this example to your situation.

- Jeff

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-07-22 : 11:31:39

Ok, and sorry for the confusion but this example uses two tables, Yourtable A and Yourtable B. The information I want to rank is in one table.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-22 : 11:38:16

they are the same table. the A and the B are alias's. In order to perform a ranking, as every example in the links given to you show, you need to do query the table twice. Did you carefully read through the article that chadmat showed you?

You might need to learn some basic SQL before you tackle a problem like this -- it is fairly advanced, and you should definitely have basic understanding of SELECT, GROUP BY, alias's, subqueries and all that before trying to solve this issue.

- Jeff

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-07-22 : 11:51:08

jsmith8858, thank you so much for your help and yes I do need further understanding of which is why I am here and have some training scheduled this coming week. But until then I have to start somewhere..

I will be searching all the above suggestions ( SELECT, GROUP BY, alias's, subqueries ), very soon.

But for now I have a process of which this one ranking issue is holding me back from completing. It is the last step in finishing a project that needs completing.

Since we have switched to SQL just the past couple of months I am trying to learn as I go and have found the people and the SQLTeam site a great resource.

As per your example I tried it:

select *
(Select count(*) from tbl_Roster
where (DateOfRank < SelectionYear) OR
(DateOfRank = SelectionYear AND DateOfRank < SelectionYear) OR
(DateOfRank = SelectionYear AND DateOfRank = SelectionYear AND YG < YG) OR
(DateOfRank = SelectionYear AND DateOfRank = SelectionYear AND YG = YG AND LastName < LastName)
)+1 as Rank
from
tbl_Roster

but got this error:

Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '+'.

Sorry to have bothered you with my Newbie issues..

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-22 : 12:00:20

you have 2 references to tbl_Roster in the same query. you need to give each one an alias to distinguish between the two. and in all of your expressions, you need to add the alias before the column name to indicate which copy of the table you wish to use.

Take a step back and re-look at your code: how is SQL supposed to know what you mean by YG < YG ?? which YG comes from which table?

Really take a close look at my example more, do not try to apply it to your scenerio until you completely understand every step of what it is doing. I know there is the tendency to cut and paste and jump right in, but trust me -- take the time to really step through the examples given and understand them fully, then try applying them.

As for syntax errors, again, do your best to solve them logically by breaking down the components of the SQL statement -- there may be mismatched parenthesis.

self-joins (which is what is occurring here) are admittedly a tough concept to learn, but the key is you are joining a table to itself (as mentioend in the KB article), and each of those two references to the same table are distinct copies and you need to indicate which copy you are pulling columns from in all of your expressions.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-22 : 12:07:11

JimAmigo --

Without even thinking or worrying about specific SQL syntax, is it clear to you how these queries are calculating the ranking?

- Jeff

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-07-22 : 12:13:53

Thank you and I will. I appreciate your help.

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-07-22 : 12:27:51

Yes very clear... the data should be ranked in ascending order. Looking first at the DateofRank field, if a tie look at the SelectionYear field, if a tie look at the YG field and last look at LastName as the final tie breaker.

Would look like this.

Rank Date SelectionYear YG LastName
1 10/20/1980 1975 2000.5 Wilkinson
2 9/16/1984 1975 2001.2 Smith
3 9/16/1984 1976 2001.2 Jenkins
4 12/15/1986 1972 2001.9 Frank

That I do understand.. LOL..

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-22 : 13:06:39

Doesn't this do the same thing?


USE Northwind
GO

CREATE TABLE myTable99([Date] datetime, SelectionYear char(4), YG decimal(5,1), LastName varchar(20))
GO
CREATE TABLE myTable00( Rank int IDENTITY(1,1), [Date] datetime, SelectionYear char(4), YG decimal(5,1), LastName varchar(20))
GO

INSERT INTO myTable99([Date], SelectionYear, YG, LastName)
SELECT '10/20/1980', '1975', 2000.5, 'Wilkinson' UNION ALL
SELECT '9/16/1984',  '1975', 2001.2, 'Smith' UNION ALL
SELECT '9/16/1984',  '1976', 2001.2, 'Zeus' UNION ALL
SELECT '9/16/1984',  '1976', 2001.3, 'Jenkins' UNION ALL
SELECT '9/16/1984',  '1976', 2001.3, 'Kaiser' UNION ALL
SELECT '12/15/1986', '1972', 2001.9, 'Frank'
GO

INSERT INTO myTable00([Date], SelectionYear, YG, LastName)
SELECT [Date], SelectionYear, YG, LastName FROM myTable99
ORDER BY [Date], SelectionYear, YG, LastName
GO

SELECT * FROM myTable00
GO

DROP TABLE myTable99
DROP TABLE myTable00
GO

Gotta check out Jeff's

Did you cut and paste and test the examples yet?

Brett

8-)

EDIT: Your choice...I just wonder which one would incurr more overhead


select B.*,
  (Select count(*) from myTable99 A
   where (A.[Date] < B.[Date]) OR
         (A.[Date] = B.[Date] AND A.SelectionYear < B.SelectionYear) OR
         (A.[Date] = B.[Date] AND A.SelectionYear = B.SelectionYear AND A.YG < B.YG) OR
         (A.[Date] = B.[Date] AND A.SelectionYear = B.SelectionYear AND A.YG = B.YG AND A.LastName < B.LastName)
  )+1 as Rank      
from
  myTable99 B

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-07-22 : 13:15:39

Yes that works.. Now I have to figure out how it works and apply it to my problem.. Thanks so much!

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-07-22 : 13:17:44

Let me see if I have figured this out.. Basically by creating the new table it inserts the identity in number order, then places the data in the new table by the way it needs to be sorted. ?

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-07-22 : 13:18:53

Overhead shouldn't be an issue because the table will have less that 500 records total.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources