April 18, 2017 at 1:40 pm
I have Data set like this
create table #Data(BID int, AID int, DID int, OV varchar(50))
insert into #Data
values (1, 10, 415, 'CP_(IKMU)_(FFFF_C2C_1)'),
(1, 10, 416, 'OO'),
(1, 20, 415, 'CP_(IKMU)_(FFFF_C2C_2)'),
(1, 20, 416, 'OO'),
(1, 70, 415, 'CP_(IKMU)_(FFFF_C2C_7)'),
(1, 70, 416, 'OO'),
(2, 30, 415, 'CP_(IKMU)_(FFFF_C2C_3)'),
(2, 30, 416, 'OO'),
(2, 40, 415, 'CP_(IKMU)_(FFFF_C2C_1)'),
(2, 40, 416, 'OO'),
(3, 50, 415, 'CP_(IKMU)_(FFFF_C2C_2)'),
(3, 50, 416, 'OO'),
(4, 60, 415, 'CP_(IKMU)_(FFFF_C2C_6)'),
(4, 60, 416, 'OO')
select * from #data d
where d.BID = 1
and not exists ( select * from #data id
where id.BID <> d.BID
and id.OV = d.OV and id.DID = d.DID
)
My Expected result is two rows but its returning one row. how to get these two rows as result set
(1, 70, 415, 'CP_(IKMU)_(FFFF_C2C_7)'),
(1, 70, 416, 'OO'),
Thanks
April 18, 2017 at 1:55 pm
In the absence of any description of selection logic which needs to be applied, this is the best I could do.
SELECT *
FROM #Data d
WHERE d.AID = 70
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 18, 2017 at 2:01 pm
Your query directly contradicts your desired results. At least one record exists that prevents that 2nd record from showing up, so your conditions for your query are not what they need to be, or your stated desired output is incorrect. You'll need to explain in detail the why behind your choice of desired output and then we can try and help figure out the SQL needed.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 18, 2017 at 2:04 pm
sgmunson - Tuesday, April 18, 2017 2:01 PMYour query directly contradicts your desired results. At least one record exists that prevents that 2nd record from showing up, so your conditions for your query are not what they need to be, or your stated desired output is incorrect. You'll need to explain in detail the why behind your choice of desired output and then we can try and help figure out the SQL needed.
Requirement is until all OV values are not matched query should return the rows base on AID
April 18, 2017 at 2:04 pm
sgmunson - Tuesday, April 18, 2017 2:01 PMYour query directly contradicts your desired results. At least one record exists that prevents that 2nd record from showing up, so your conditions for your query are not what they need to be, or your stated desired output is incorrect. You'll need to explain in detail the why behind your choice of desired output and then we can try and help figure out the SQL needed.
Requirement is until all OV values are not matched query should return the rows base on AID
April 18, 2017 at 2:16 pm
inayatkhan - Tuesday, April 18, 2017 2:04 PMRequirement is until all OV values are not matched query should return the rows base on AID
Please try again. Write in pseudo code if it helps.
'Until all values are not matched' makes it sound like they start off matching and gradually become unmatched. I don't understand that at all.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 18, 2017 at 2:53 pm
inayatkhan - Tuesday, April 18, 2017 2:04 PMRequirement is until all OV values are not matched query should return the rows base on AID
maybe something like this:
WITH grouped AS
(SELECT BID, AID, COUNT(*) OVER (PARTITION BY DID, OV) AS cnt
FROM #Data)
SELECT d.BID, d.AID, d.DID, d.OV
FROM #Data d
INNER JOIN grouped g ON d.BID = g.BID AND d.AID = g.AID
WHERE d.BID = 1
AND g.cnt = 1;
April 19, 2017 at 6:01 am
Chris Harshman - Tuesday, April 18, 2017 2:53 PMinayatkhan - Tuesday, April 18, 2017 2:04 PMRequirement is until all OV values are not matched query should return the rows base on AIDmaybe something like this:
WITH grouped AS
(SELECT BID, AID, COUNT(*) OVER (PARTITION BY DID, OV) AS cnt
FROM #Data)
SELECT d.BID, d.AID, d.DID, d.OV
FROM #Data d
INNER JOIN grouped g ON d.BID = g.BID AND d.AID = g.AID
WHERE d.BID = 1
AND g.cnt = 1;
Thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply