Ranking data by columns - 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)

Ranking data by columns

Author

Topic

hnomani
Starting Member

35 Posts

Posted - 2003-03-04 : 12:47:55

We are using a table that list Accts with scores. Accts are stored in one column and scores are stored in other columns.

Ex.

CREATE TABLE ACCT_SCORE (ACCT INT, SCORE1 INT, SCORE2 INT, SCORE3 INT)
GO

INSERT INTO ACCT_SCORE VALUES(100, 25, 10, 45)
GO
INSERT INTO ACCT_SCORE VALUES(101, 16, 18, 25)
GO
INSERT INTO ACCT_SCORE VALUES(102, 45, 17, 25)
GO
INSERT INTO ACCT_SCORE VALUES(103, 7, 5, 15)
GO
INSERT INTO ACCT_SCORE VALUES(104, 18, 22, 28)
GO

SELECT * FROM ACCT_SCORE
GO

ACCT SCORE1 SCORE2 SCORE3
100 25 10 45
101 16 18 25
102 45 17 25
103 7 5 15
104 18 22 28

We want to sort data for each acct by their score and display 1 for Min. score and so on. The final output from the query should look like,

-- FINAL OUTPUT

ACCT SCORE1 SCORE2 SCORE3
100 2 1 3
101 1 2 3
102 3 1 2
103 2 1 3
104 1 2 3

We need help in constructing a query that will yield this type of result set, any suggestions are greatly appreciated.

Thanks
Haseeb

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-04 : 13:24:04

You need some ugly CASE expressions at first glance...

SELECT Acct
CASE WHEN Score1 > Score2 AND Score1 > Score3 THEN 1
WHEN Score1 > Score2 OR Score > Score3 THEN 2
ELSE 3 END as Score1,
CASE WHEN Score2 > Score1 AND Score2 > Score3 THEN 1
WHEN Score2 > Score1 OR Score2 > Score3 THEN 2
ELSE 3 END as Score2,
CASE WHEN Score3 > Score1 AND Score3 > Score2 THEN 1
WHEN Score3 > Score1 OR Score3 > Score2 THEN 2
ELSE 3 END as Score2
FROM
ACCT_SCORE

Do accounts ever have ties? Are there ever more than 3 scores? Consider normalizing your data as well making Scores rows instead of columns.

- Jeff

hnomani
Starting Member

35 Posts

Posted - 2003-03-04 : 14:30:39

Thanks!
The account can have a tie, and if the scores have a tie then we are going to use a different logic to break the tie, like Score1 over score2 and so on.
The table I have posted is just a model table, the table we have in our system have 9 columns.
I like the second suggestion you made about making the column values as rows and then we can do an ORDER BY. I will try that & see if I can get anywhere with it!

Thanks Again,
Haseeb

quote:

You need some ugly CASE expressions at first glance...

SELECT Acct
CASE WHEN Score1 > Score2 AND Score1 > Score3 THEN 1
WHEN Score1 > Score2 OR Score > Score3 THEN 2
ELSE 3 END as Score1,
CASE WHEN Score2 > Score1 AND Score2 > Score3 THEN 1
WHEN Score2 > Score1 OR Score2 > Score3 THEN 2
ELSE 3 END as Score2,
CASE WHEN Score3 > Score1 AND Score3 > Score2 THEN 1
WHEN Score3 > Score1 OR Score3 > Score2 THEN 2
ELSE 3 END as Score2
FROM
ACCT_SCORE

Do accounts ever have ties? Are there ever more than 3 scores? Consider normalizing your data as well making Scores rows instead of columns.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources