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

Q&A about the site for biology researchers, academics, and students

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 ID, Name, family_id, items_sold
FROM cte
where row_num = 1
Order by items_sold DESC

fork query

edited nov 14 anonymous

created oct 3 12 Mahmoud Gamal