Reputation: 587
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);
Upvotes: 3
Views: 1875
Reputation: 311188
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
Upvotes: 6
Reputation: 1612
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.
Upvotes: 3