October 8, 2003 at 2:52 pm
I have a ranking table created as follows using identity
SELECT
RANK=IDENTITY(INT),
SITE,
MGR_NAME,
COLL_NAME,
SUM_DMM_PTS,
SUM_NSF_PTS,
SUM_DMM_PTS + SUM_NSF_PTS NET_DMM_PTS
INTO #RANKINGS
FROM #DMM LEFT JOIN #NSF ON #DMM.EMPNO = #NSF.EMPNO
WHERE 1=2
INSERT INTO #RANKINGS
SELECT
SITE,
MGR_NAME,
COLL_NAME,
SUM_DMM_PTS,
SUM_NSF_PTS,
SUM_DMM_PTS + SUM_NSF_PTS NET_DMM_PTS
FROM #DMM LEFT JOIN #NSF ON #DMM.EMPNO = #NSF.EMPNO
ORDER BY 6 DESC
It’s possible that two people have the same value for Net_DMM_PTS, so I want to display the MIN RANK for both of them.
Here’s how I am doing it, but I would like to use a single select statement without building yet another Temp Table.
Help?
SELECT
NET_DMM_PTS,
MIN(RANK)AS RANK
INTO #MINRANK
FROM #RANKINGS
GROUP BY
NET_DMM_PTS
SELECT
R.SITE,
R.MGR_NAME,
R.COLL_NAME,
R.SUM_DMM_PTS,
R.SUM_NSF_PTS,
R.NET_DMM_PTS,
M.RANK
FROM
#RANKINGS R
INNER JOIN #MINRANK M
ON R.NET_DMM_PTS = M.NET_DMM_PTS
ORDER BY 1,2,6
J. Moseley
[font="Courier New"]ZenDada[/font]
October 8, 2003 at 3:39 pm
The methods oulined here:
http://support.microsoft.com/?kbid=186133
will give you "golf tournament" types of ranking, where ties get the same rank and then skip the next rank number. Test the performance vs. your identity method, though, as the set-based methods can be slow with large tables.
But I think a need like this should be handled at the presentation layer, not in SQL...
--Jonathan
--Jonathan
October 8, 2003 at 3:58 pm
Great ideas for all kinds of ranking! I am sticking with Ken Henderson's solution using the temp table, but reading the Microsoft article gave me the idea to query the result using a derived table - exactly what I needed!
SELECT
SITE,
MGR_NAME,
COLL_NAME,
SUM_DMM_PTS,
SUM_NSF_PTS,
R.NET_DMM_PTS,
M.RANK
FROM
#RANKINGS R,
(
SELECT
NET_DMM_PTS,
MIN(RANK)AS RANK
FROM #RANKINGS
GROUP BY
NET_DMM_PTS
)M
WHERE M.NET_DMM_PTS = R.NET_DMM_PTS
ORDER BY 1,2,6
J. Moseley
[font="Courier New"]ZenDada[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply