Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Find max and min where count of unique > x... Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, January 31, 2014 2:07 AM
 Forum 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 thisValue Type Identifier1225 ABC 2178401225 ABC 1216621295 ABC 2755811225 ABC 2178401220 ABC 2337051225 ABC 1639651000 ABC 3078711225 ABC 1216621225 ABC 1639651225 ABC 3052041295 ABC 2755821295 ABC 275583and 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: 1295I 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 Group: General Forum Members Last Login: 2 days ago @ 11:05 PM Points: 220, Visits: 533
 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 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 MinABC 1295 1000In the table you built, it should give me Max 1295 and min 1225, which are the values with 3+ unique identifiers for the ABC typeAm i using it wrong or?Any other ideas?
Post #1536692
 Posted Friday, January 31, 2014 4:17 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: 2 days ago @ 10:36 PM Points: 108, Visits: 137
 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 ) tabWHERE cnt>2GROUP BY type`
Post #1536702
 Posted Friday, January 31, 2014 4:24 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: 2 days ago @ 10:36 PM Points: 108, Visits: 137
 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 ) tabWHERE cnt>2GROUP BY type`
Post #1536705
 Posted Friday, January 31, 2014 4:54 AM
 Forum 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 Group: General Forum Members Last Login: 2 days ago @ 10:36 PM Points: 108, Visits: 137
 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 )tGROUP BY type`
Post #1536726
 Posted Friday, January 31, 2014 5:29 AM
 SSCertifiable Group: General Forum Members Last Login: 2 days ago @ 9:46 AM Points: 6,641, Visits: 12,612
 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) dGROUP 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1536731
 Posted Friday, January 31, 2014 8:33 AM
 SSCrazy Group: General Forum Members Last Login: Today @ 9:20 PM Points: 2,523, Visits: 5,354
 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 LeacockForum 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 Group: General Forum Members Last Login: 2 days ago @ 9:46 AM Points: 6,641, Visits: 12,612
 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1536851

 Permissions