So I need to find an individual rank for a record

I have these results


  • 524, 21056, 1, WI
  • 523, 20973, 2, WI
  • 477, 21411, 3, WI
  • 460, 20714, 4, WI
  • 394, 20974, 5, WI
  • plus a hundred more

I just want to have the result be one record, where the join_number is.. lets say: 20714 (rank 4) and show 460, 20714, 4

I found some sql with the find_in_set, but it didn't exist in my version, so I am stumped. Right now, this is all I have that works.

SELECT  JOIN_NUMBER, thestate, EmpTot,
     RANK() OVER (PARTITION BY thestate ORDER BY EmpTot DESC) AS RankResult 
FROM    thistable
WHERE   State = 'wi'
  • It's not clear what you mean by the query above "working", or entirely clear what your exact goal is. This would be easier to answer with such information, – RDFozz Sep 15 '17 at 18:37
  • I need to find an individual records rank in a database table. the below code is great, but I have to make it dynamic now. – Adam Sep 20 '17 at 14:08

Using a CTE, you can filter your windowed results

([EmpTot]      INT,
 [state]       CHAR(2)

(524, 21056, 'WI'),
(523, 20973, 'WI'),
(477, 21411, 'WI'),
(460, 20714, 'WI'),
(394, 20974, 'WI');

WITH cte
                RANK() OVER(PARTITION BY [state] ORDER BY [EmpTot] DESC) AS [RankResult]
         FROM [#temp]
         WHERE [state] = 'WI')
     SELECT EmpTot,JOIN_NUMBER,RankResult
     FROM [cte]
     WHERE [JOIN_NUMBER] = 20973;

DROP TABLE [#temp];
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.