August 26, 2012 at 9:32 am
Hi all,
How do you find a group where number of rows in the group are gtr than 1 and at least one of the non grouped columns contain at least one row with a certain value?
Example:
tableA has columns name, Type, price
example data:
nameA, typeA, 1.00
nameA, typeB, 2.75
nameB, typeA, 2.00
nameC, typeB, 3.00
In this example, I would need to identify a group on name where the grouping contains at least one row where Type = 'typeB'. The value in price is ultimately what I need when there is a group with count of rows > 1 AND Type = typeB, else the group should just use the price from typeA when there is no type B within a group with count of rows > 1.
--this group I need to identify since nameA contains at least one value of typeB in column Type. When typeB is present in the group, use the price from typeB
nameA, typeA, 1.00
nameA, typeB, 2.75
--this is not a group, and therefore only uses the price from typeA since there is no value of typeB in column Type.
nameB, typeA, 2.00
--this is not a group, and does have a typeB. Use this price.
nameC, typeB, 3.00
The returned result set would only include:
nameA, typeB, 2.75
nameB, typeA, 2.00
nameC, typeB, 3.00
Any help would be appreciated.
Thanks!
August 26, 2012 at 6:32 pm
Something like this perhaps?
DECLARE @t TABLE (Name VARCHAR(10), [TYPE] VARCHAR(10), Price MONEY)
INSERT INTO @t
SELECT 'nameA', 'typeA', 1.00
UNION ALL SELECT 'nameA', 'typeB', 2.75
UNION ALL SELECT 'nameB', 'typeA', 2.00
UNION ALL SELECT 'nameC', 'typeB', 3.00
;WITH CTE AS (
SELECT Name, [TYPE], Price
,n=ROW_NUMBER() OVER (PARTITION BY Name ORDER BY CASE [TYPE] WHEN 'typeB' THEN 0 ELSE 1 END)
FROM @t
)
SELECT Name, [TYPE], Price
FROM CTE
WHERE n=1
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 27, 2012 at 8:34 am
Thank you so much!
August 28, 2012 at 12:08 pm
For this,
SELECT retailer_name, blood_type, blood_price,
COUNT(*) OVER (PARTITION BY retailer_name)
AS inventory_cnt
FROM Vampire_Retailers AS VR
WHERE blood_type = 'B';
Wouldn't you instead get nameA, 'B', 2.75, 1 and not nameA, 'B', 2.75, 2 due to the where clause?
August 28, 2012 at 1:29 pm
Indeed, the query is wrong and it won't take you near to the solution.
I suggest you to avoid that opinion and stay with Dwain's solution.
August 28, 2012 at 3:08 pm
Any of that seems like overkill to me for the specific problem stated. Why not just?:
SELECT
name,
ISNULL(MAX(CASE WHEN type = 'typeB' THEN 'typeB' END), MAX(CASE WHEN type <> 'typeB' THEN type END)) AS type,
ISNULL(MAX(CASE WHEN type = 'typeB' THEN price END), MAX(CASE WHEN type <> 'typeB' THEN price END)) AS price
FROM dbo.tablename
GROUP BY
name
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 28, 2012 at 7:06 pm
ScottPletcher (8/28/2012)
Any of that seems like overkill to me
Well, these are the Vampire_Retailers now aren't they?
Loved that one Joe!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 28, 2012 at 10:31 pm
Thank you all for helping. The simple and direct answers gave me options for the solution. Thanks again!
August 29, 2012 at 12:19 am
--Check this
SELECT Name,TYPE,Price FROM
(SELECT Name, [TYPE], Price
,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY PRICE DESC) AS N FROM @t) AS A
WHERE N=1
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply