How to extract common values from each archives?

GianPiero · 2019-01-12 08:16:29 UTC

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 · 2019-01-12 09:05:54 UTC

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 · 2019-01-12 15:59:04 UTC

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 · 2019-01-12 16:05:16 UTC

Jeff

Can we make this dynamic ???

If i am understanding
you correctly

jeffw8713 · 2019-01-12 16:06:37 UTC

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 · 2019-01-12 16:15:21 UTC

Now that I know who you are JEFF

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

Amazing

JeffModen · 2019-01-12 16:21:00 UTC

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 · 2019-01-12 16:23:02 UTC

Gotcha Jeff

Thanks

JeffModen · 2019-01-12 16:25:52 UTC

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 · 2019-01-12 16:29:02 UTC

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 · 2019-01-12 16:47:39 UTC

got you Jeff

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

helping people

JeffModen · 2019-01-12 17:00:37 UTC

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 · 2019-01-12 23:19:37 UTC

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

JeffModen · 2019-01-13 04:18:05 UTC

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 · 2019-01-13 08:48:55 UTC

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 · 2019-01-13 19:41:35 UTC

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 · 2019-01-13 20:22:44 UTC

Wow Jeff^2.
The Titans
JEFF X 2

JeffModen · 2019-01-13 20:37:43 UTC

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 · 2019-01-14 05:34:54 UTC

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 · 2019-01-14 08:38:16 UTC

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