SELECT *,
   RANK() OVER(ORDER BY col1) as [Rank]
FROM YourTable;

SELECT *,
   RANK() OVER(ORDER BY col1) + COUNT(*) OVER(PARTITION BY col1) -1
FROM YourTable

quote:

---

Originally posted by gavakie

Right now when i run it that way it has the highest ranked person as 73 and everyeone else 2

---

CREATE TABLE #tmp (col1 INT);
INSERT INTO #tmp VALUES (10),(11),(31),(31),(31),(48),(48),(100);

SELECT *,
   RANK() OVER(ORDER BY col1) as [Rank]
FROM #tmp ORDER BY [Rank];

SELECT *,
   RANK() OVER(ORDER BY col1) + COUNT(*) OVER(PARTITION BY col1) -1 as [Rank]
FROM #tmp ORDER BY [Rank];

SELECT *,
   RANK() OVER(ORDER BY col1 DESC) as [Rank]
FROM #tmp ORDER BY [Rank];

SELECT *,
   RANK() OVER(ORDER BY col1 DESC) + COUNT(*) OVER(PARTITION BY col1) -1 as [Rank]
FROM #tmp ORDER BY [Rank];

DROP TABLE #tmp