How to get rows that have sourctype 484456 only when group by GivenPartNumber_No

Question

Post reply

How to get rows that have sourctype 484456 only when group by GivenPartNumber_No

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

February 23, 2022 at 6:46 pm

#3987618

I work on sql server 2012 i need to get rows that have source type 484456

when group by two columns group by GivenPartNumber_Non and vcompanyid

so i need to make select query display every group of rows by GivenPartNumber_Non and vcompanyid have source type 484456 only

may be this group one row or 2rows or 3 rows or more etc any way i need to retrieve it .

create table #notmappedsources
 (
 GivenPartNumber_Non varchar(200),
 vcompanyid  int,
 SourceType int
 )
 insert into #notmappedsources(GivenPartNumber_Non,vcompanyid,SourceType)
 values
 ('ADFGH22',1233,484456),
 ('ADFGH22',1233,484456),
 ('ADFGH22',1233,484456),
    
 ('XFDY990',5489,484456),
 ('XFDY990',5489,484456),
    
 ('GX84322',2549,484456),
    
    
    
    
 ('LKHG23',3201,484320),
 ('LKHG23',3201,484320),
 ('LKHG23',3201,484320),
    
 ('DGHJ66',7041,484320),
    
 ('FDYH36',8901,484320),
 ('FDYH36',8901,484320),
    
 ('MNH32',5601,489561),
 ('MNH32',5601,489561),
 ('MNH32',5601,484456),
    
 ('NUI34',9076,489561),
    
 ('KLMD33',5022,489561),
 ('KLMD33',5022,484456)

expected result as below :

This topic was modified 2 years, 10 months ago by ahmed_elbarbary.2010.

March 4, 2022 at 7:42 am

This was removed by the editor as SPAM
March 4, 2022 at 7:44 am

This was removed by the editor as SPAM

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

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

Michael L John One Orange Chip Points: 27060 More actions · Answer 1

SELECT GivenPartNumber_Non, vcompanyid int, SourceType

FROM #notmappedsources

WHERE SourceType = 484456

This is very basic.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 2

thank you for reply

it not return my expected result above

please run that query

SELECT GivenPartNumber_Non, vcompanyid int, SourceType

FROM #notmappedsources

WHERE SourceType = 484456

it will give rows extra as

MNH32                     5601      484456
KLMD33                    5022      484456

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 3

i need to get groups that have only one source as 484456

but groups have multi source i don't need it

so below

MNH32 5601 484456

KLMD33 5022 484456

must not display because it have other sources

kaj SSCommitted Points: 1607 More actions · Answer 4

Maybe something like this?

WITH SinglesourcePartCompany AS (
    SELECT GivenPartNumber_Non,  vcompanyid
    FROM #notmappedsources
    GROUP BY GivenPartNumber_Non,  vcompanyid
    HAVING COUNT(DISTINCT sourcetype) = 1
)
SELECT nms.GivenPartNumber_Non,  nms.vcompanyid, nms.SourceType
FROM #notmappedsources nms
INNER JOIN SinglesourcePartCompany spc ON spc.GivenPartNumber_Non = nms.GivenPartNumber_Non AND spc.vcompanyid = nms.vcompanyid
WHERE nms.SourceType = 484456

Steve Collins SSCrazy Points: 2173 More actions · Answer 5

Similar to kaj's code

with unq_prs(GivenPartNumber_Non,vcompanyid) as (
    select GivenPartNumber_Non,vcompanyid
    from #notmappedsources
    group by GivenPartNumber_Non,vcompanyid
    having count(*)=sum(iif(SourceType=484456, 1, 0)))
select n.*
from #notmappedsources n
     join unq_prs up on n.GivenPartNumber_Non=up.GivenPartNumber_Non
                        and n.vcompanyid=up.vcompanyid;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Ed B Hall of Fame Points: 3669 More actions · Answer 6

SELECT GivenPartNumber_Non, vcompanyid, MAX(SourceType) AS 'SourceType'
FROM   #notmappedsources
GROUP  BY GivenPartNumber_Non, vcompanyid
HAVING MAX(SourceType) = 484456
AND    MIN(SourceType) = 484456

and for fun.

DECLARE @SourceType INT = 484456

SELECT GivenPartNumber_Non, vcompanyid, @SourceType AS  'SourceType'
FROM   #notmappedsources
WHERE  SourceType = 484456
EXCEPT
SELECT GivenPartNumber_Non, vcompanyid, @SourceType
FROM   #notmappedsources
WHERE  SourceType <> 484456

This reply was modified 2 years, 10 months ago by Ed B.

Michael L John One Orange Chip Points: 27060 More actions · Answer 7

ahmed_elbarbary.2010 wrote:

thank you for reply
it not return my expected result above
please run that query
SELECT GivenPartNumber_Non, vcompanyid int, SourceType
FROM #notmappedsources
WHERE SourceType = 484456
it will give rows extra as
MNH32                     5601      484456
KLMD33                    5022      484456

Whoops. I didn't pay attention to what you asked. You have two solutions!

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Robert7266 Newbie Points: 4 More actions · Answer 8

Please check this query:

;WITH CTE AS

(

SELECT *,RANK()OVER(PARTITION BY GivenPartNumber_Non,vcompanyid ORDER BY SourceType) Rank_ASC

,RANK()OVER(PARTITION BY GivenPartNumber_Non,vcompanyid ORDER BY SourceType DESC) Rank_DESC

FROM #notmappedsources

)SELECT GivenPartNumber_Non, vcompanyid,SourceType

FROM CTE

WHERE SourceType = 484456 AND Rank_ASC=Rank_DESC