Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

find a grouping with at least one row within the group containing a certain value Expand / Collapse
Author
Message
Posted Sunday, August 26, 2012 9:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:11 PM
Points: 43, Visits: 411
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!
Post #1350117
Posted Sunday, August 26, 2012 6:32 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:53 PM
Points: 3,438, Visits: 5,390
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1350170
Posted Monday, August 27, 2012 8:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:11 PM
Points: 43, Visits: 411
Thank you so much!
Post #1350425
Posted Monday, August 27, 2012 4:39 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:12 PM
Points: 1,945, Visits: 3,180
>> 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? <<

Where is the DDL ? What is the key? What are the data types? Do you work with specs like this? Let's play with it and you can learn ISO-11179 rules. Here is DDL with the stuff you left out:

CREATE TABLE Vampire_Retailers
(retailer_name VARCHAR(10) NOT NULL,
blood_type CHAR(3)NOT NULL,
PRIMARY KEY (retailer_name, blood_type
blood_price DECIMAL (5,2) NOT NULL
CHECK (blood_price > 0.00));

Did I guess right?

INSERT INTO Vampire_Retailers
VALUES ('nameA', 'A', 1.00),
('nameA', 'B', 2.75),
('nameB', 'B', 2.00),
('nameC', 'B', 3.00);

SELECT retailer_name, 'B', blood_price
FROM Vampire_Retailers AS VR
WHERE blood_type = 'B'

>> In this example, I would need to identify a group on retailer_name where the grouping contains at least one row where blood_type = 'B'. The value in price is ultimately what I need when there is a group with count of rows > 1 AND blood_type = 'B', else the group should just use the price from 'B' when there is no blood_type B within a group with count of rows > 1.


I am not sure what that means in the sample data, but try this query to get those counts and then do some filtering on it.

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';

nameA, 'B', 2.75, 2
nameB, 'B', 2.00, 1
nameC, 'B', 3.00, 1


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1350642
Posted Tuesday, August 28, 2012 12:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:11 PM
Points: 43, Visits: 411
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?

Post #1351166
Posted Tuesday, August 28, 2012 1:29 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:35 PM
Points: 4,046, Visits: 9,202
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.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1351213
Posted Tuesday, August 28, 2012 3:08 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 2,330, Visits: 3,509
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1351286
Posted Tuesday, August 28, 2012 7:06 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:53 PM
Points: 3,438, Visits: 5,390
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1351358
Posted Tuesday, August 28, 2012 10:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:11 PM
Points: 43, Visits: 411
Thank you all for helping. The simple and direct answers gave me options for the solution. Thanks again!
Post #1351382
Posted Wednesday, August 29, 2012 12:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:13 AM
Points: 210, Visits: 1,211
--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
Post #1351411
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse