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 max and min where count of unique > x... Expand / Collapse
Author
Message
Posted Friday, January 31, 2014 2:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:41 AM
Points: 3, Visits: 7
Hi,

I have a table looking like this

Value Type Identifier
1225 ABC 217840
1225 ABC 121662
1295 ABC 275581
1225 ABC 217840
1220 ABC 233705
1225 ABC 163965
1000 ABC 307871
1225 ABC 121662
1225 ABC 163965
1225 ABC 305204
1295 ABC 275582
1295 ABC 275583

and a million other records with different identifiers, types and values.

I would like to find the MAX(Value) and MIN(Value) for each Type where there is at least 3 unique identifiers per value.

So a row with min and max for the type "ABC" would look like:

Min: 1225 Max: 1295

I have tried a lot with the "having count(distinct Identifier) > 2" but it surely counts all of the identifers, regardless of the value.

Any ideas?
Post #1536669
Posted Friday, January 31, 2014 3:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:29 AM
Points: 220, Visits: 537
I came up with the following solution

CREATE TABLE #TEMP (Value int,Type nvarchar(50),Identifier int)

INSERT INTO #TEMP VALUES(1225,'ABC', 217840)
INSERT INTO #TEMP VALUES (1225,'ABC', 121662)
INSERT INTO #TEMP VALUES (1295,'ABC', 275581)
INSERT INTO #TEMP VALUES (1225,'ABC', 217840)
INSERT INTO #TEMP VALUES (1220,'ABC', 233705)
INSERT INTO #TEMP VALUES (1225,'ABC', 163965)
INSERT INTO #TEMP VALUES (1000,'ABC', 307871)
INSERT INTO #TEMP VALUES (1225,'ABC', 121662)
INSERT INTO #TEMP VALUES (1225,'ABC', 163965)
INSERT INTO #TEMP VALUES (1225,'ABC', 305204)
INSERT INTO #TEMP VALUES (1295,'ABC', 275582)
INSERT INTO #TEMP VALUES (1295,'ABC', 275583)
INSERT INTO #TEMP VALUES (1000,'DEF', 275583)




;WITH cte
AS (SELECT type,
Count(type) AS cnt
FROM #temp
GROUP BY type)
SELECT t.type,
Max(value),
Min(value)
FROM #temp t
INNER JOIN cte
ON t.type = cte.type
WHERE cnt > 3
GROUP BY t.type



Post #1536689
Posted Friday, January 31, 2014 3:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:41 AM
Points: 3, Visits: 7
Thanks for that, but unfortunately it does not produce what i need :-(


Type Max Min
ABC 1295 1000

In the table you built, it should give me Max 1295 and min 1225, which are the values with 3+ unique identifiers for the ABC type

Am i using it wrong or?

Any other ideas?
Post #1536692
Posted Friday, January 31, 2014 4:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:58 AM
Points: 123, Visits: 156
Try this:
CREATE TABLE #TEMP (Value int,Type nvarchar(50),Identifier int)

INSERT INTO #TEMP VALUES(1225,'ABC', 217840)
INSERT INTO #TEMP VALUES (1225,'ABC', 121662)
INSERT INTO #TEMP VALUES (1295,'ABC', 275581)
INSERT INTO #TEMP VALUES (1225,'ABC', 217840)
INSERT INTO #TEMP VALUES (1220,'ABC', 233705)
INSERT INTO #TEMP VALUES (1225,'ABC', 163965)
INSERT INTO #TEMP VALUES (1000,'ABC', 307871)
INSERT INTO #TEMP VALUES (1225,'ABC', 121662)
INSERT INTO #TEMP VALUES (1225,'ABC', 163965)
INSERT INTO #TEMP VALUES (1225,'ABC', 305204)
INSERT INTO #TEMP VALUES (1295,'ABC', 275582)
INSERT INTO #TEMP VALUES (1295,'ABC', 275583)
INSERT INTO #TEMP VALUES (1000,'DEF', 275583)

SELECT type, MIN(value), MAX(value)
FROM(
SELECT Type, value, COUNT(identifier) over(partition by value) cnt
FROM (
SELECT DISTINCT type, value, identifier
FROM #TEMP
) t
) tab
WHERE cnt>2
GROUP BY type

Post #1536702
Posted Friday, January 31, 2014 4:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:58 AM
Points: 123, Visits: 156
correctly
SELECT type, MIN(value), MAX(value) 
FROM(
SELECT Type, value, COUNT(identifier) over(partition by type, value) cnt
FROM (
SELECT DISTINCT type, value, identifier
FROM #TEMP
) t
) tab
WHERE cnt>2
GROUP BY type

Post #1536705
Posted Friday, January 31, 2014 4:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:41 AM
Points: 3, Visits: 7
Thank you very very much...

And omg (as the young ones writes ;) ), i need to update my T-SQL - Partition, when did that arrive
Post #1536715
Posted Friday, January 31, 2014 5:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:58 AM
Points: 123, Visits: 156
OR
SELECT type, MIN(value), MAX(value) 
FROM(
SELECT Type, value, COUNT(DISTINCT identifier) cnt
FROM #TEMP
GROUP BY type, value
HAVING COUNT(DISTINCT identifier)>2
)t
GROUP BY type

Post #1536726
Posted Friday, January 31, 2014 5:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:40 AM
Points: 6,777, Visits: 12,883
Always carefully test any solutions offered to you. The one you rejected works with the sample set, the one you appear to have accepted does not. Here's a third offering:
SELECT [Type], MIN(MINValue), MAX(MAXValue)
FROM (
SELECT [Type], Identifier, MINValue = MIN(Value), MAXValue = MAX(Value)
FROM #TEMP
GROUP BY [Type], Identifier
) d
GROUP BY [Type]
HAVING COUNT(*) > 2



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1536731
Posted Friday, January 31, 2014 8:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:48 PM
Points: 2,774, Visits: 5,949
Chris, I think you were expecting different results than the ones the OP asked for.
I would go for the last query from o.fimin.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1536807
Posted Friday, January 31, 2014 9:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:40 AM
Points: 6,777, Visits: 12,883
Luis Cazares (1/31/2014)
Chris, I think you were expecting different results than the ones the OP asked for.
I would go for the last query from o.fimin.


Heh I see it now.
"In the table you built, it should give me Max 1295 and min 1225, which are the values with 3+ unique identifiers for the ABC type"
should read
"In the table you built, it should give me Max 1295 and min 1225, which are the values with 3+ unique identifiers for the value"

Thanks Luis, and apologies to the OP.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1536851
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse