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

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-31 : 07:31:03

Matt writes "I have an application that I would like to be able to rank the results returned from the database. For Example

SELECT * FROM table WHERE val1=val1 OR val2=val2 OR val3=val3 OR val3=val3 OR val4=val4

If all values match I would like the rank to be 100. If only 3 values match then the rank would be 75. If only 2 values match then the rank would be 50, and so on.

Right now I am using a CASE statement to achieve this effect, but I know there has to be a faster, simpler solution.

Thanks,
Matt"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-31 : 08:14:56

This may not be simpler, but it might be faster... I think I would still go with the case statement if its only 4 conditions though.

Select Key, Rank = sum(Rank)
From
(
SELECT key, Rank=25 FROM table WHERE val1=val1 Union All
SELECT key, Rank=25 FROM table WHERE val2=val2 Union All
SELECT key, Rank=25 FROM table WHERE val3=val3 Union All
SELECT key, Rank=25 FROM table WHERE val4=val4
) A
Group By Key

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-31 : 10:47:32

or something like:


SELECT * , (CASE when val1=val1 THEN 1 ELSE 0 END + 
           CASE when val2=val2 THEN 1 ELSE 0 END +
           CASE when val3=val3 THEN 1 ELSE 0 END +
           CASE when val4=val4 THEN 1 ELSE 0 END) / 4.0 as Pct
FROM table 
WHERE val1=val1 OR val2=val2 OR val3=val3 OR val3=val3 OR val4=val4

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-31 : 11:52:44

Am I the only one wondering what this WHERE clause is doing?
"WHERE val1=val1 OR val2=val2 OR val3=val3 OR val3=val3 OR val4=val4"
Especially this part: "val3=val3 OR val3=val3"
It looks like the WHERE clause would alway be true, unless one of the values is NULL, and ANSI_NULLS is on.

The same question goes for this answer:
"CASE when val1=val1 THEN 1 ELSE 0 END"
When will this not return a 1, except when it is NULL?

Matt, I think you need to restate what it is you are trying to do.

CODO ERGO SUM

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-31 : 12:16:06

My assumption was that by "val1=val1" he meant "val1=@val1" or something like that. But you are correct, you never know for sure where some of these questions are coming for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources