two guys from kc

TSQL provides a TOP command to retrieve a portion of the result set, but no MIDDLE command.  You can use partitioning (NTILE) to mimic the MIDDLE command.

I created a simple four record data set (scores) for this example:

bob, 12
mary, 15
jim, 19
lenny, 29

And here’s the code that divides the result set (ordered by score) into four partitions and uses WHERE to limit the set to partition 2 and 3 (the middle 50%).

;WITH ScoreRanking AS
(
SELECT *, NTILE(4) OVER (ORDER BY Score) [Position]
FROM Scores
)

SELECT PlayerID, Score
FROM ScoreRanking
WHERE Position IN 2, 3

Tags: MSSQL Transact TSQL "MS SQL"

In MSSQL. You can leave a response, or trackback from your site.