How to extract common values from each archives?

GianPiero · January 12, 2019, 8:16am

I have a sql tab with this structure

CREATE TABLE #ARCHPRES(PRESID INT, IDEMITT INT)

In this sql table is stored a configuration like below

INSERT INTO #ARCHPRES VALUES
(1,22),
(1,32),
(1,42),
(1,223),
(1,250),
(2,54),
(2,57),
(2,223),
(2,250),
(3,171),
(3,223),
(3,250),
(4,182),
(4,185),
(4,188),
(4,191),
(4,223),
(4,250),
(5,171),
(5,223),
(5,250),
(6,182),
(6,185),
(6,188),
(6,191),
(6,223),
(6,250)

I would like to extract from this sql table only common values among each and all (presid) archives

In short, my end result should return be 223 and 250, how can i do that?

Sorry for my english as it's not my native language.

harishgg1 · January 12, 2019, 9:05am

hi i tried this

hope it helps

drop create data ..

drop table #ARCHPRES
go 

CREATE TABLE #ARCHPRES(PRESID INT, IDEMITT INT)

--In this sql table is stored a configuration like below

INSERT INTO #ARCHPRES VALUES
(1,22),
(1,32),
(1,42),
(1,223),
(1,250),
(2,54),
(2,57),
(2,223),
(2,250),
(3,171),
(3,223),
(3,250),
(4,182),
(4,185),
(4,188),
(4,191),
(4,223),
(4,250),
(5,171),
(5,223),
(5,250),
(6,182),
(6,185),
(6,188),
(6,191),
(6,223),
(6,250)
go

SQL ..

select IDEMITT from #ARCHPRES where PRESID = 1 
intersect 
select IDEMITT from #ARCHPRES where PRESID = 2
intersect
select IDEMITT from #ARCHPRES where PRESID = 3
intersect
select IDEMITT from #ARCHPRES where PRESID = 4 
intersect 
select IDEMITT from #ARCHPRES where PRESID = 5
intersect
select IDEMITT from #ARCHPRES where PRESID = 6
go

JeffModen · January 12, 2019, 3:59pm

Heh... now try it for a half million unique PRESIDs, harishgg1. Or even seven. It means the code isn't flexible enough to handle even a single change which is pretty much contrary to what code is for.

harishgg1 · January 12, 2019, 4:05pm

Jeff

Can we make this dynamic ???

If i am understanding
you correctly

jeffw8713 · January 12, 2019, 4:06pm

If you only want that column returned:

 Select a.IDEMITT
   From #ARCHPRES a
  Group By
        a.IDEMITT
 Having count(*) = (Select count(Distinct presid)
                      From #ARCHPRES a2);

Now - if you want to return the row associated with each match:

   With ids
     As (
 Select *
      , idCount = row_number() over(Partition By a.idemitt Order By presid)
   From #ARCHPRES a
        )
 Select a.PRESID
      , a.IDEMITT
   From ids
  Inner Join #ARCHPRES a On a.PRESID = ids.PRESID And a.IDEMITT = ids.IDEMITT
  Where idCount = (Select count(Distinct presid) From #ARCHPRES a);

harishgg1 · January 12, 2019, 4:15pm

Now that I know who you are JEFF

I started " noticing your name "
in several articles I browsed for finding TSQL solutions

Amazing

JeffModen · January 12, 2019, 4:21pm

Your English is just fine. Better than that, your post is great because you've provided readily consumable data. Thank you for that.

The DDL for the table doesn't show something that will be necessary to make this easy and so I'll have to say that I assumed (we know what happens when you do that) that the combination of the PRESID and IDEMITT columns do, in fact, create unique pairs. If they don't, then the following solution won't work. If they do, then the following solution will only need to make 2 passes at the table regardless of how many unique PRESIDs there are.

 SELECT IDEMITT
   FROM #ARCHPRES
  GROUP BY IDEMITT
 HAVING COUNT(IDEMITT) = (SELECT COUNT(DISTINCT PRESID) FROM #ARCHPRES)
;

harishgg1 · January 12, 2019, 4:23pm

Gotcha Jeff

Thanks

JeffModen · January 12, 2019, 4:25pm

Heh.... doesn't matter if you know me or not... doesn't matter who would say such a thing... they would be correct in the observation that the code was hardcoded for just one scenario instead of all scenarios that the data could manifest itself as.

I do thank you for the very nice compliment but it's just code and I don't look at things so differently as others. Look at what @jeffw8713 posted for this problem for immediate proof of that.

JeffModen · January 12, 2019, 4:29pm

While I was typing, it appears that I came up with the exact same code as what @jeffw8713 did. He also came up with an anticipated/unspoken but likely requirement of needing to return the whole rows that met the need. Nicely done.

harishgg1 · January 12, 2019, 4:47pm

got you Jeff

You love the code !!!!
coding debugging testing PART
all the different things RELATED to code

helping people

JeffModen · January 12, 2019, 5:00pm

That's exactly correct. The other "Jeff" on this thread appears to be of the same ilk.

And thank you very much for taking my comments the right way. A whole lot of people don't. They're only meant to teach and are never meant as ad hominem attacks.

GianPiero · January 12, 2019, 11:19pm

Great and thanks
this solution is perfect and yes
PRESID and IDEMITT is the table index.
Thanks again JeffModen

JeffModen · January 13, 2019, 4:18am

Thank you for the feedback on all of that but I can't take all of the credit here. Although we were suffering from proverbial "ships passing in the night", @jeffw8713 did actually post the very same solution first. It turns out that I actually know him and have had several discussions with him in the past. He's one smart cookie.

harishgg1 · January 13, 2019, 8:48am

hi

i know this has been given as a solution ....

I can think of a scenario where data would break this
Please correct me if i am wrong

SELECT IDEMITT
FROM #ARCHPRES
GROUP BY IDEMITT
HAVING COUNT(IDEMITT) = (SELECT COUNT(DISTINCT PRESID) FROM #ARCHPRES)
;

i mean dups for the same id 1

Overall Counts Match
but result incorrect

PRESID            IDEMITT
1                          44
                           44
2 ...................... nothing here 
3                        44
4                        44
5                        44
6                       44

i think this can be resolved by
HAVING COUNT(distinct IDEMITT) =

JeffModen · January 13, 2019, 7:41pm

Good thinking and I absolutely agree. That's why I said in my writeup when I posted my code that I assumed that the combination of PRESID and IDEMITT did form UNIQUE pairs (and that implies an index to enforce it) and that the code would break if that weren't true. The OP did come back after I posted my code and stated that the index I implied of did, in fact, exist. If it didn't, then we'd have to do like you said.

yosiasz · January 13, 2019, 8:22pm

Wow Jeff^2.
The Titans
JEFF X 2

JeffModen · January 13, 2019, 8:37pm

Thank you for the kind words (I'm humbled) but I'm no "Titan"... I'm just a fellow that has made more mistakes than most and figured out how to fix my mistakes and pass that on to others.

harishgg1 · January 14, 2019, 5:34am

Thank you for the kind words (I'm humbled) but I'm no "Titan"... I'm just a fellow that has made more mistakes than most and figured out how to fix my mistakes and pass that on to others.

Nice words Jeff
Like your humility

I totally agree with you

harishgg1 · January 14, 2019, 8:38am

Wow Jeff^2.
The Titans
JEFF X 2

yosiasz

Want to become a TITAN
very very easy !!!

!!!!! ANYONE CAN

24 hrs day 365 days a year