Find max and min where count of unique > x...

Question

Post reply

Find max and min where count of unique > x...

kjo 75029

SSC Rookie

Points: 41
More actions
January 31, 2014 at 2:07 am

#285071

Hi,
I have a table looking like this
ValueTypeIdentifier
1225ABC217840
1225ABC121662
1295ABC275581
1225ABC217840
1220ABC233705
1225ABC163965
1000ABC307871
1225ABC121662
1225ABC163965
1225ABC305204
1295ABC275582
1295ABC275583
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?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 1

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

kjo 75029 SSC Rookie Points: 41 More actions · Answer 2

Thanks for that, but unfortunately it does not produce what i need 🙁

Type Max Min

ABC12951000

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?

o.fimin SSChasing Mays Points: 602 More actions · Answer 3

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

o.fimin SSChasing Mays Points: 602 More actions · Answer 4

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

kjo 75029 SSC Rookie Points: 41 More actions · Answer 5

Thank you very very much...

And omg (as the young ones writes 😉 ), i need to update my T-SQL - Partition, when did that arrive :hehe:

o.fimin SSChasing Mays Points: 602 More actions · Answer 6

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

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.
General Disclaimer:
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?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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