asked
How to limit results returned
Using SQL Server 2000. I want to limit the values returned by the query below to all categorynames but only the first five productnames for each category. use Northwind select categoryname, productname from categories c join products p on c.categoryid = p.categoryid order by categoryname, productname Thanks in advance, P. Ratcliff
How about unitinstock for the top 5. So like this result set but only for the top five in each category: use Northwind select categoryname, productname, unitsinstock from categories c join products p on c.categoryid = p.categoryid order by categoryname, unitsinstock desc, productname cheers!
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
here you are one example with cte: CREATE TABLE #Category ( categoryid int ,categoryname varchar(100) ) INSERT INTO #Category SELECT 1,'car' UNION SELECT 2,'food' UNION SELECT 3,'chair' UNION SELECT 4,'table' UNION SELECT 5,'pencil' SELECT * FROM #Category CREATE TABLE #products ( productID int, categoryid int, Productname varchar(100), unitsinstock int ) INSERT INTO #products SELECT 1,1,'product_1',5 UNION SELECT 2,1,'product_1',1 UNION SELECT 3,1,'product_1',12 UNION SELECT 4,1,'product_1',2 UNION SELECT 5,1,'product_1',5 UNION SELECT 6,1,'product_1',3 UNION SELECT 7,1,'product_1',8 UNION SELECT 8,2,'product_1',55 UNION SELECT 9,2,'product_1',125 UNION SELECT 10,2,'product_1',1 UNION SELECT 11,2,'product_1',17 UNION SELECT 12,2,'product_1',2 UNION SELECT 13,2,'product_1',364 UNION SELECT 14,2,'product_1',5 UNION SELECT 15,2,'product_1',11 SELECT * FROM #products --final select WITH cte AS ( select categoryname, productname, unitsinstock, ROW_NUMBER() OVER(PARTITION BY categoryname ORDER BY unitsinstock DESC) AS RowNumber from #Category c join #products p on c.categoryid = p.categoryid ) SELECT * FROM cte WHERE RowNumber
Second solution (SQL 2000) using cursor: DECLARE @categoryname varchar(100) CREATE TABLE #Table_1 ( ID int ,categoryname varchar(100) ,Productname varchar(100) ,unitsinstock int ) --final select DECLARE cur_1 CURSOR FOR select categoryname from categories OPEN cur_1 FETCH NEXT FROM cur_1 INTO @categoryname WHILE @@FETCH_STATUS = 0 BEGIN SELECT RowNumber = IDENTITY(INT,1,1), categoryname, productname, unitsinstock INTO #Table from categories c join products p on c.categoryid = p.categoryid WHERE categoryname = @categoryname order by unitsinstock desc INSERT INTO #Table_1 SELECT * FROM #Table DROP TABLE #Table FETCH NEXT FROM cur_1 INTO @categoryname END CLOSE cur_1 DEALLOCATE cur_1 GO SELECT * FROM #Table_1 WHERE ID
16 People are following this question.
