This forum has been so helpful helping me get several queries built I really appreciate it.
Here is the new problem:
I created a tblTopTenFinalResults. In this table are 2000 records broken into around 50 different Measures with the top ten rates for different organizations (OrgID).
What I am trying to capture is for each Measure ID, I need the ranking for OrgID 129. If OrgID 129 came in 3rd place for Measure ID 1, I need that. If OrgID 129 came in 10th place in measure 4, 7, 8 etc I need to capture that for a graph that I will eventually build showing what place OrgID 129 placed in all of the Measures.
Below if my first stab at the SQL to rank all of the OrgIDs by each Measure ID. However, when I try to run it, I get an error saying a subquery can return more than one filed without using the EXISTS reserved word in the main query's FROM clause.
Where would I put the code to only count the ranking for OrgID 129?
SELECT tblTopTenFinalResults.[Measure ID], tblTopTenFinalResults.Rate, tblTopTenFinalResults.OrgID, (Select Q.[Measure ID], Q.[OrgID], Q.[Rate], (SELECT COUNT(*) FROM
[tblTopTenFinalResults] Q1
WHERE Q1.[Measure ID] = Q.[Measure ID]
AND Q1.[Rate] <Q.[Rate]) +1 AS Rank
FROM tblTopTenFinalResults AS Q
ORDER BY Q.[Measure ID], Q.Rate, Q.OrgID;) AS OrgRanking
FROM tblTopTenFinalResults
ORDER BY tblTopTenFinalResults.[Measure ID], tblTopTenFinalResults.Rate DESC;
Tidak ada komentar:
Posting Komentar