Find N element in sequence using SQL
Given the following table:
Sequence Tag ----- ---- 1 a 2 a 3 a 88 a 100 a 1 b 7 b 88 b 101 b
I would like a query that returns the 4th in each sequence of tags (ordered by Tag, Sequence asc):
Tag 4thInSequence ----- --开发者_运维百科------ a 88 b 101
What is the most efficient SQL I can use here? (Note: SQL Server 2008 tricks are allowed)
WITH Enumerated AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Tag ORDER BY Sequence) AS RN
FROM MyTable
)
SELECT * FROM Enumerated WHERE RN = 4;
精彩评论