Query using the TOP keyword. 1

[brick02]

Hello, I have a question about how to construct a specific query (using the TOP keyword?)

I'll reduce the problem to it's essence:

given is a table of sales records containing
sales_record_id, agent_id, item_id, solditems
There are no restrictions on how many times an agent or an item can occur, but every combination agent_id-item_id occurs exactly once.

For every agent I want his/her top 5 (or less) most sold items as a result. This has to be available in a view, so no variables or cursors allowed.
Version is SQL Server 2005.

Thanks for helping me out.


Brick.

 

[RiverGuy]

SELECT a.*
FROM
	(SELECT agent_id, item_id, SUM(SoldItems) AS SoldItems
	FROM SalesTable
	GROUP BY agent_id, item_id) a

WHERE SoldItems BETWEEN

(SELECT TOP 1 * FROM
	(SELECT TOP 5 SoldItems
	FROM
		(SELECT agent_id, item_id, SUM(SoldItems) AS SoldItems
		FROM SalesTable
		GROUP BY agent_id, item_id) b1
		WHERE agent_id = a.agent_id
		ORDER BY SoldItems DESC) b2
	ORDER BY SoldItems ASC)

AND

(SELECT TOP 1 SoldItems
FROM
		(SELECT agent_id, item_id, SUM(SoldItems) AS SoldItems
		FROM SalesTable
		GROUP BY agent_id, item_id) a1
	WHERE agent_id = a.agent_id
	ORDER BY SoldItems DESC)

ORDER BY agent_id, SoldItems DESC

 

[brick02]

This is genius, it did the job for me.
thank you!

 

[markros]

Try also (SQL Server 2005 and up)

select * from (select Agent, Item, Rank() over (partition by Agent, Item ORDER by SoldItem DESC) as Rank) AllItemsSoldRanked where Rank <=5

From the top of my head and not tested.