November 25, 2008 at 4:35 am
This seems doable but I can't get it to work. Would appreciate if someone could take a look...
My table is something like
ID ProductID UnitID
1 1 101
2 1 101
3 1 102
4 1 103
5 1 104
6 2 104
7 2 103
8 2 104
9 2 104
I am trying to pull out the most occurring of productID and UnitID
So from the table below I want
ProductID UnitID
1 101
2 104
I can do
select COUNT(*) AS cnt, ProductID, UnitID from Products group by ProductID, UnitID
but this is giving me as I'd expect
cnt ProductID UnitID
2 1 101
1 1 102
1 1 103
1 1 104
3 2 104
1 2 103
Any Ideas please?
November 25, 2008 at 5:55 am
You can use the CTE -
with CTE1(cnt,unitid,productId) as
(select COUNT(*) AS cnt, UnitID,productId from Products group by UnitID,productId)
select productId,unitId from CTE1 where cnt > 1
November 25, 2008 at 5:59 am
The question intrigued me, so I played with until I got the right answer. I've no doubt (at all) that there are more efficient ways to work this, but this works
WITH X AS (SELECT productid
,unitid
,COUNT(productId) CountProductId
,COUNT(UnitId) CountUnitId
FROM MyTest AS mt
GROUP BY productid
,mt.UnitId
)
SELECT a.productid
,a.unitid
FROM X AS a
JOIN x AS b
ON a.productid = b.productid
AND a.unitid = b.unitid
AND a.countproductid = (SELECT MAX(countproductid)
FROM x b2
WHERE b2.productid = b.productid
)
AND a.countunitid = (SELECT MAX(countunitid)
FROM x b2
WHERE b2.unitid = b.unitid
)
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 25, 2008 at 6:22 am
abhijeetv & Grant - thanks for taking the trouble to reply however neither solutions seem to do what I want... I'll reread my post to make sure I've explained myself properly
November 25, 2008 at 6:37 am
kalikal (11/25/2008)
I am trying to pull out the most occurring of productID and UnitIDSo from the table below I want
ProductID UnitID
1 101
2 104
Maybe I misunderstood, but my query returns exactly these results based on the information you provided. I've got a sample table based on your sample data. The query works to return exactly those rows as defined.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 25, 2008 at 7:01 am
Hi Grant
My actuals products table contains 1485 rows. If I do a
select distinct productID, unitID from products
I get 121 records
and
select distinct productID from products
I get 93 rows.
So I would expect 93 rows back which contain the ProductID and the UnitID which occurs the most for each ProductID
Running your sql I'm only getting 6 rows and I can't spot why it is doing that.
Regards
Kal
November 25, 2008 at 8:17 am
Oh, that's a different problem. I got you a max for the unique combination of values. Just to get a max for each ProductId drop the second sub-select in the query I wrote. I think that will do the trick.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 25, 2008 at 8:44 am
Hi Grant
I had to make a slight change but it now works - thank you for taking the trouble to help.
WITH X AS (SELECT productid
,unitid
,COUNT(productId) CountProductId
,COUNT(UnitId) CountUnitId
FROM products AS mt
GROUP BY productid
,mt.UnitId
)
SELECT a.productid
,a.unitid
FROM X AS a
JOIN x AS b
ON a.productid = b.productid
AND a.unitid = b.unitid
AND a.unitid = (SELECT MAX(unitid)
FROM x b2
WHERE b2.productid = b.productid)
November 25, 2008 at 9:15 am
kalikal (11/25/2008)
Hi GrantI had to make a slight change but it now works - thank you for taking the trouble to help.
WITH X AS (SELECT productid
,unitid
,COUNT(productId) CountProductId
,COUNT(UnitId) CountUnitId
FROM products AS mt
GROUP BY productid
,mt.UnitId
)
SELECT a.productid
,a.unitid
FROM X AS a
JOIN x AS b
ON a.productid = b.productid
AND a.unitid = b.unitid
AND a.unitid = (SELECT MAX(unitid)
FROM x b2
WHERE b2.productid = b.productid)
One point, In the sub-select where you're getting the max value, you're not getting the max UnitId as you outlined are you? You would want the Max UnitIdCount, right?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 25, 2008 at 9:23 am
Hi Grant
I dodn't actually need the actual count itself - I just needed max(unitID) - the count itself wasn't necessary.
Thanks
Kal
November 25, 2008 at 9:23 am
Hi Grant
I didn't actually need the actual count itself - I just needed max(unitID) - the count itself wasn't necessary.
Thanks
Kal
November 25, 2008 at 9:45 am
Wow, I really misunderstood then. I thought you were going for the most frequent occurence.
Well then, here's one more suggestion. In the sub-query, replace the MAX with a TOP 1 and an ORDER BY DESC on the UnitId instead of the MAX and GROUP BY. You'll probably see better performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 26, 2008 at 11:08 am
May I suggest this alternative solution. In my opinion more simple and more lean on i/o (4 logical reads vs 7).
select
PR. ProductID,
PR. UnitId,
Tmp. maxCnt
from
Products PR
join
(
select
ProductID,
max(Cnt) maxCnt
from
(
select
ProductID,
UnitID,
count(*) as Cnt
from
Products
group by
ProductID,
UnitID
)
Tmp
group by
ProductID
)
Tmp on Tmp. ProductID = PR. ProductID
group by
PR. ProductID,
PR. UnitID,
Tmp. maxCnt
having
count(*) = maxCnt
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply