Get every second row as a result table in t-sql

问题:

I'm looking for a t-sql script that returns a list, that shows every second value from a grouping from Table1.

For example I have the following data (Table1) and want the desired result-list:

Table1:
Customer    Quantity
A           5
A           8               (*)
B           3
B           5                (*)
B           11
C           7
D           4
D           23               (*)

Desired retult-list:

Customer    Quantity
A           8
B           5
D           23

I think about doing something something with 'select distinct and left outer join', but I can't get it to work. Possibly I need an row numbering, but can't figure out how to do it. Anyone can help me?

Beneath is the script I used to make and fill Table1:

CREATE TABLE Table1
(Customer nvarchar(1) NULL,
Quantity int NOT NULL);

INSERT INTO Table1(Customer,Quantity)
VALUES 
('A',5),
('A',8),
('B',3),
('B',5),
('B',11),
('C',7),
('D',4),
('D',23);

回答1:

You can use ROW_NUMBER and a CTE:

WITH data AS (
    SELECT  *, ROW_NUMBER() OVER  (PARTITION BY Customer ORDER BY Quantity) rn
    FROM    @Table1 
)
SELECT Customer, Quantity
FROM    data 
WHERE   rn = 2

How it works:

Using ROW_NUMBER() will assign a sequential number to each row based on what's specified in OVER (). In OVER i specify to PARTITION the rows on customer, that means each group of data on same customer will be numberered separately. Then ORDER BY Quantity mean it should order the data based on quantity for each customer - so i can get the 2nd row for each customer ordered by quantity.

回答2:

This can be done quite easily using the row_number window function:

SELECT customer, quantity
FROM   (SELECT customer, quantity,
               ROW_NUMBER() OVER (PARTITION BY customer 
                                  ORDER BY quantity ASC) AS rn
        FROM   table1) t
WHERE  rn = 2