Find n largest values in a column

0

Please login or register to vote for this query.

(click on this box to dismiss)

http://stackoverflow.com/questions/12705789/find-n-largest-values-in-a-column-in-microsoft-mysql

Open Data

Q&A for developers and researchers interested in open data

DECLARE @Products TABLE(ID INT, family_id INT, Name VARCHAR(50), items_sold INT);
DECLARE @families TABLE (ID INT, name VARCHAR(50));

INSERT INTO @Products VALUES
(1, 1, 'A', 10),
(2, 1, 'B', 15),
(3, 2, 'C', 12),
(4, 3, 'D', 20),
(5, 3, 'E', 22),
(6, 3, 'F', 8),
(7, 4, 'G', 7),
(8, 1, 'H', 3),
(9, 2, 'I', 17),
(10, 4, 'J', 24);

;WITH cte
AS
(
	SELECT *, ROW_NUMBER() OVER(PARTITION BY family_ID ORDER BY items_sold DESC) row_num
	FROM @Products
)
SELECT * FROM cte
where row_num = 1
Order by family_ID

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results