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

Transact-SQL (2000)

Ranking entries

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-04-11 : 08:10:43

JustinG writes "I want to rank Sites in their class and group them in their region afterwards. This query works but I wanted to know if theres a better way of doing this.

Region 1
Site - Class - RANK - SCORE
KL Main (KLH) - S1 - 10 - 10.000
Bukit Bintang (BKB) - S1 - 9 - 20.000
Jalan Ipoh (IPR) - S1 - 8 - 30.000
Seremban (SMB) - S2 - 1 - 100.000

Region 2
Petaling Jaya (PJA) - S1 - 7 - 40.000
Subang Jaya (SBJ) - S1 - 6 - 50.000
Klang (KLG) - S2 - 2 - 9.375
Damansara (DSR) - S2 - 3 - 8.750
Puchong (PUJ) - S2 - 4 - 8.125

Region 3
Penang-Drowning (PGH) - S1 - 5 - 60.000
Penang - Bayan Barus - S2 - 5 - 7.500
Sungai Petani (SNP) - S2 - 6 - 6.875
Butterworth (BTW) - S2 - 7 - 6.250
Ipoh (IPH) - S1 - 4 - 70.000
Taiping (TPG) - S2 - 8 - 5.625
Cameron Highlands (CAM)- S3 - 1 - 100.000
Teluk Intan (TLK) - S3 - 2 - 90.000
Kota Bahru (KTB) - S3 - 3 - 80.000
Kuala Trengganu (KTR) - S3 - 4 - 70.000

...theres more regions but i left them out

SQL:
CREATE TABLE #temp
(
[id] INT identity(1, 1),
sSiteName VARCHAR(50),
sSiteCategoryName VARCHAR(50),
iClass INT,
iRank INT,
bScore DECIMAL(6, 2)
)

INSERT INTO #temp(sSiteName, sSiteCategoryName, iClass, iRank, bScore)
SELECT sSiteName,
sSiteCategoryName,
CASE WHEN sSiteName < 'j' THEN 1
WHEN (sSiteName >= 'j' AND sSiteName < 'q') THEN 2
ELSE 3
END iClass,
NULL,
AVG(bScore)
FROM Site S,
SiteCategory SC,
Answer A
WHERE A.iSiteId = S.iSiteId AND
S.iSiteCategoryId = SC.iSiteCategoryId AND
fDiscard = 0 AND
fIncomplete = 0
GROUP BY sSiteName,
sSiteCategoryName
ORDER BY iClass,
AVG(bScore),
sSiteName

SELECT sSiteCategoryName,
sSiteName,
iClass,
(
SELECT COUNT(*) + 1
FROM #temp t2
WHERE t2.iClass = t1.iClass AND
t2.bScore < t1.bScore
) iRank,
bScore
FROM #temp t1
ORDER BY sSiteCategoryName,
iClass,
iRank
DROP TABLE #temp"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-04-11 : 19:49:06

Hi Justin

Happy to help, but could you post a couple of rows from SiteCategory and Answer tables?

And maybe some example results

PS - I'm assuming the example data you posted is in the Site table?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources