Using Partitioning in TSQL to Mimic a MIDDLE Command
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



1 Comment