Execute the following Microsoft T-SQL example scripts in SQL Server Management Studio Query Editor to demonstrate how to find a specific member of a list.

USE AdventureWorks;
-- Use row_number to assign a SeqNo identifier to each item in the list
SELECT   ProductName = p1.Name,
                  p1.ListPrice,
                  ROW_NUMBER()
                    OVER(ORDER BY p1.ListPrice DESC) AS SeqNo
         FROM     Production.Product p1
                  JOIN Production.Product p2
                    ON p1.ListPrice >= p2.ListPrice
                       AND p1.ListPrice > 0.0
         GROUP BY p1.Name,
                  p1.ListPrice
GO
/* Partial results
 
ProductName             ListPrice   SeqNo
Road-150 Red, 62        3578.27     1
Road-150 Red, 44        3578.27     2
Road-150 Red, 48        3578.27     3
Road-150 Red, 52        3578.27     4
Road-150 Red, 56        3578.27     5
Mountain-100 Silver, 38 3399.99     6
Mountain-100 Silver, 42 3399.99     7
Mountain-100 Silver, 44 3399.99     8
*/
 
-- Selection query for the nth item
-- SQL Common Table Expression - CTE - Select 4th item
DECLARE  @n INT
SET @n = 4;
 
WITH ctePriceList
     AS (SELECT   ProductName = p1.Name,
                  p1.ListPrice,
                  ROW_NUMBER()
                    OVER(ORDER BY p1.ListPrice DESC) AS SeqNo
         FROM     Production.Product p1
                  JOIN Production.Product p2
                    ON p1.ListPrice >= p2.ListPrice
                       AND p1.ListPrice > 0.0
         GROUP BY p1.Name,
                  p1.ListPrice)
SELECT ProductName,
       ListPrice
FROM   ctePriceList
WHERE  SeqNo= @n;
GO
 
/* Results
 
ProductName             ListPrice
Road-150 Red, 52        3578.27

*/