April 19, 2012 at 4:54 am
Hi
Is it possible to have a query to return top 10 items from each division.
Example Table columns:
Division, ItemNo,Name,transType, qty, cost
I want to have a count of records for each items from each division and select top 10 items from each division
A simple query of
SELECT ItemNo,Name,COUNT(ItemNo) AS TheCount FROM Table1
GROUP BY Division,ItemNo,Name
will bring all items but need to filter for only the top 10 of each division.
TIA
April 19, 2012 at 5:05 am
Hi,
I think this lends itself well to using SQL Servers ROW_NUMBER() function. An option within this Ranking function allows the data to be partitioned. And the derived Row_Number can be referenced if declared within a Common Table Expression.
Kind Regards
April 19, 2012 at 5:14 am
SELECT TOP 10 ItemNo,Name,COUNT(ItemNo) AS TheCount FROM Table1
GROUP BY Division,ItemNo,Name
order by COUNT(ItemNo) desc
Edit: Sorry, just saw for each division.
Something like this should steer you in the right direction (untested as you haven't provided sample data):
WITH a AS ( SELECT Division ,
ItemNo ,
Name ,
COUNT(ItemNo) AS TheCount
FROM Table1
GROUP BY Division ,
ItemNo ,
NAME
),
b AS ( SELECT Division ,
ItemNo ,
NAME ,
ROW_NUMBER() OVER ( PARTITION BY Division ORDER BY TheCount DESC ) rn
FROM a
)
SELECT *
FROM b
WHERE rn <= 10
April 19, 2012 at 5:48 am
Thank you very much.
The problem solved using the method suggested and it worked very well. Trying to comprehend the sql;-)
Thanks again
April 19, 2012 at 4:50 pm
CTE is good solution for recursions and if you need same expression more than once, but this is not the case here.
So, the same thing without CTE:
SELECT it.*
FROM
(SELECT items.*,
rn = ROW_NUMBER() OVER ( PARTITION BY items.Division ORDER BY items.TheCount DESC )
FROM
(SELECT t.Division, t.ItemNo, t.Name, TheCount = COUNT(t.ItemNo)
FROM dbo.Table1 t
GROUP BY t.Division, t.ItemNo, t.NAME
) items
) it
WHERE it.rn <= 10
April 19, 2012 at 4:57 pm
Vedran Kesegic (4/19/2012)
CTE is good solution for recursions and if you need same expression more than once, but this is not the case here.So, the same thing without CTE:
SELECT it.*FROM
(SELECT items.*,
rn = ROW_NUMBER() OVER ( PARTITION BY items.Division ORDER BY items.TheCount DESC )
FROM
(SELECT t.Division, t.ItemNo, t.Name, TheCount = COUNT(t.ItemNo)
FROM dbo.Table1 t
GROUP BY t.Division, t.ItemNo, t.NAME
) items
) it
WHERE it.rn <= 10
Vedran, your query and the CTE and going to produce the exact same plan and is going to use the exact same resource. A non-recursive CTE is as good and as similar as sub-queries.
April 19, 2012 at 11:39 pm
Yes, but CTE bring limitations: it wont work in a view, and it works on less SQL versions that without CTE. So, my choice is to use a technique when I really need it.
April 19, 2012 at 11:49 pm
Vedran Kesegic (4/19/2012)
Yes, but CTE bring limitations: it wont work in a view, and it works on less SQL versions that without CTE. So, my choice is to use a technique when I really need it.
Totally agree; But given the fact ROW_NUMBER cannot be used anywhere but greater than SQL 2005 versions, the current code is not portable too. So judicial use of features will be the best fit.
April 20, 2012 at 2:02 am
Vedran Kesegic (4/19/2012)
Yes, but CTE bring limitations: it wont work in a view, and it works on less SQL versions that without CTE. So, my choice is to use a technique when I really need it.
Eh?
USE tempdb
go
CREATE VIEW test AS
WITH a AS (SELECT name FROM master.sys.columns)
SELECT * FROM a;
GO
SELECT * FROM test
April 20, 2012 at 2:21 am
Sorry, it will work in a view. My bad.
April 20, 2012 at 2:30 am
Vedran Kesegic (4/19/2012)
Yes, but CTE bring limitations: it wont work in a view, and it works on less SQL versions that without CTE. So, my choice is to use a technique when I really need it.
No offence meant, but that's not true. You've used a ranking function which was introduced in SQL Server 2005 but won't use a CTE (which was introduced in SQL Server 2005) because you want to support more versions of SQL Server? Howard has already covered that CTEs can be used in a view as well.
April 20, 2012 at 2:35 am
Cadavre (4/20/2012)
Vedran Kesegic (4/19/2012)
Yes, but CTE bring limitations: it wont work in a view, and it works on less SQL versions that without CTE. So, my choice is to use a technique when I really need it.No offence meant, but that's not true. You've used a ranking function which was introduced in SQL Server 2005 but won't use a CTE (which was introduced in SQL Server 2005) because you want to support more versions of SQL Server? Howard has already covered that CTEs can be used in a view as well.
And i already coverd that ranking function usage 😀 Just for a little gag, Craig! No pun intended! 😎
April 20, 2012 at 2:49 am
ColdCoffee (4/20/2012)
Cadavre (4/20/2012)
Vedran Kesegic (4/19/2012)
Yes, but CTE bring limitations: it wont work in a view, and it works on less SQL versions that without CTE. So, my choice is to use a technique when I really need it.No offence meant, but that's not true. You've used a ranking function which was introduced in SQL Server 2005 but won't use a CTE (which was introduced in SQL Server 2005) because you want to support more versions of SQL Server? Howard has already covered that CTEs can be used in a view as well.
And i already coverd that ranking function usage 😀 Just for a little gag, Craig! No pun intended! 😎
Ah yes, so you did. Need to keep drinking my morning coffee I think. . .
I've been struggling to come up with any algorithms for a design issue this morning which is probably further proof of needing coffee.
April 20, 2012 at 2:55 am
Cadavre (4/20/2012)
I've been struggling to come up with any algorithms for a design issue this morning which is probably further proof of needing coffee.
And its deep into the night, 2 AM here, im struggling to write down specifications for one parallel processing job engine! I need some shots of tequila to straighten my mind :w00t:
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply