January 9, 2015 at 10:09 pm
Hi,
My example data as this:
ID code1 code2
1 a m
1 b n
1 c T
2 d m
2 e n
2 f n
3 g m
3 h n
3 i T
I would like to have a select stmt that check for all
same ID and code2 does not contain T, then return the records
For example data: the query should return
2 d m
2 e n
2 f n
Is it possible to do this? Please advised.
Thanks,
January 9, 2015 at 10:29 pm
Dee Dee-422077 (1/9/2015)
Hi,My example data as this:
ID code1 code2
1 a m
1 b n
1 c T
2 d m
2 e n
2 f n
3 g m
3 h n
3 i T
I would like to have a select stmt that check for all
same ID and code2 does not contain T, then return the records
For example data: the query should return
2 d m
2 e n
2 f n
Is it possible to do this? Please advised.
Thanks,
Quite few ways of doing this, here is a simple solution
😎
USE tempdb;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA(ID,code1,code2) AS
( SELECT * FROM (VALUES
(1,'a','m')
,(1,'b','n')
,(1,'c','T')
,(2,'d','m')
,(2,'e','n')
,(2,'f','n')
,(3,'g','m')
,(3,'h','n')
,(3,'i','T')
) AS X(ID,code1,code2)
)
,SAMPLE_T_FLAG AS
(
SELECT
SD.ID
,SD.code1
,SD.code2
,MIN(ABS(ASCII(SD.code2) - 84)) OVER
(
PARTITION BY SD.ID
) AS T_FLAG
FROM SAMPLE_DATA SD
)
SELECT
ST.ID
,ST.code1
,ST.code2
FROM SAMPLE_T_FLAG ST
WHERE ST.T_FLAG > 0;
Results
ID code1 code2
----------- ----- -----
2 d m
2 e n
2 f n
January 9, 2015 at 10:38 pm
Here is another even simpler solution!
declare @t table
(
ID int
, code1 char
, code2 char
)
insert @t values
(1,'a','m')
,(1,'b','n')
,(1,'c','T')
,(2,'d','m')
,(2,'e','n')
,(2,'f','n')
,(3,'g','m')
,(3,'h','n')
,(3,'i','T')
;with cte as
(
Select id
from @t
where code2 = 'T'
)
select t1.id, t1.code1, t1.code2
from @t t1
where not exists (select 1 from cte c where c.id = t1.id)
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 10, 2015 at 10:34 am
LinksUp (1/9/2015)
Here is another even simpler solution!
declare @t table
(
ID int
, code1 char
, code2 char
)
insert @t values
(1,'a','m')
,(1,'b','n')
,(1,'c','T')
,(2,'d','m')
,(2,'e','n')
,(2,'f','n')
,(3,'g','m')
,(3,'h','n')
,(3,'i','T')
;with cte as
(
Select id
from @t
where code2 = 'T'
)
select t1.id, t1.code1, t1.code2
from @t t1
where not exists (select 1 from cte c where c.id = t1.id)
True enough but that does seem to require prior knowledge that "T" qualifies as the exception. Thee whole purpose of such code should be to not require such prior knowledge.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2015 at 10:38 am
Eirikur Eiriksson (1/9/2015)
Dee Dee-422077 (1/9/2015)
Hi,My example data as this:
ID code1 code2
1 a m
1 b n
1 c T
2 d m
2 e n
2 f n
3 g m
3 h n
3 i T
I would like to have a select stmt that check for all
same ID and code2 does not contain T, then return the records
For example data: the query should return
2 d m
2 e n
2 f n
Is it possible to do this? Please advised.
Thanks,
Quite few ways of doing this, here is a simple solution
😎
USE tempdb;GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA(ID,code1,code2) AS
( SELECT * FROM (VALUES
(1,'a','m')
,(1,'b','n')
,(1,'c','T')
,(2,'d','m')
,(2,'e','n')
,(2,'f','n')
,(3,'g','m')
,(3,'h','n')
,(3,'i','T')
) AS X(ID,code1,code2)
)
,SAMPLE_T_FLAG AS
(
SELECT
SD.ID
,SD.code1
,SD.code2
,MIN(ABS(ASCII(SD.code2) - 84)) OVER
(
PARTITION BY SD.ID
) AS T_FLAG
FROM SAMPLE_DATA SD
)
SELECT
ST.ID
,ST.code1
,ST.code2
FROM SAMPLE_T_FLAG ST
WHERE ST.T_FLAG > 0;
Results
ID code1 code2----------- ----- -----
2 d m
2 e n
2 f n
Hmmm. Same thing here. Requires prior knowledge that the "T" is the exception. I realize that's exactly what the OP asked for but was that just because of the example he gave or does he want to look for a hardcoded "T" exception?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2015 at 10:39 am
Dee Dee-422077 (1/9/2015)
Hi,My example data as this:
ID code1 code2
1 a m
1 b n
1 c T
2 d m
2 e n
2 f n
3 g m
3 h n
3 i T
I would like to have a select stmt that check for all
same ID and code2 does not contain T, then return the records
For example data: the query should return
2 d m
2 e n
2 f n
Is it possible to do this? Please advised.
Thanks,
Are you actually looking for a hardcoded "T" or are you looking for the fact that ID 2 is simply different than ID 1 and 3?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2015 at 10:51 am
Or this (assuming that we know 'T' is the exception) :
WITH TestData as (
SELECT * FROM (VALUES
(1,'a','m')
,(1,'b','n')
,(1,'c','T')
,(2,'d','m')
,(2,'e','n')
,(2,'f','n')
,(3,'g','m')
,(3,'h','n')
,(3,'i','T')
) AS X(ID,code1,code2)
)
select
*
from
TestData td
where
not exists(select
1
from
TestData td1
where
td1.ID = td.ID and
td1.code2 = 'T');
January 10, 2015 at 11:35 am
Based on Jeff's posts and making some assumptions that are not expressed in the original post I have another solution.
Assumptions:
All ID are groups of three
Return those groups that are unique in the groups
--WITH TestData as (
--SELECT * FROM (VALUES
-- (1,'a','m')
-- ,(1,'b','n')
-- ,(1,'c','T')
-- ,(2,'d','m')
-- ,(2,'e','n')
-- ,(2,'f','n')
-- ,(3,'g','m')
-- ,(3,'h','n')
-- ,(3,'i','T')
-- ) AS X(ID,code1,code2)
--)
--select
-- *
--into
-- dbo.TestData
--from
-- TestData td;
with basedata as (
select
ID,
code1,
code2,
rn = row_number() over (partition by ID order by code1)
from
dbo.TestData
), CrossTab as (
select
ID,
max(case rn when 1 then code2 else null end) code21,
max(case rn when 2 then code2 else null end) code22,
max(case rn when 3 then code2 else null end) code23
from
basedata
group by
ID
), GroupData as (
select
ca.ID,
ct.code21,
ct.code22,
ct.code23
from
CrossTab ct
cross apply(select ct1.ID from CrossTab ct1 where ct1.ID <> ct.ID)ca(ID)
), DiffData as (
select * from CrossTab
except
select * from GroupData
)
select
td.*
from
dbo.TestData td
where
exists(select 1 from DiffData dd where dd.ID = td.ID);
January 10, 2015 at 11:39 am
Jeff Moden (1/10/2015)
Requires prior knowledge that the "T" is the exception. I realize that's exactly what the OP asked for but was that just because of the example he gave or does he want to look for a hardcoded "T" exception?
Just like in any good menuless restaurant, cut, prepared, cooked and served as ordered;-)
😎
January 13, 2015 at 2:09 pm
Thank you all, Your solutions work for me
Best,
Dee
January 14, 2015 at 1:09 pm
Eirikur Eiriksson (1/10/2015)
Jeff Moden (1/10/2015)
Requires prior knowledge that the "T" is the exception. I realize that's exactly what the OP asked for but was that just because of the example he gave or does he want to look for a hardcoded "T" exception?Just like in any good menuless restaurant, cut, prepared, cooked and served as ordered;-)
😎
Presumably, at some point the exception character will be known, so instead of hardcoding 'T', you could use a parameter like:
select t1.id, t1.code1, t1.code2
from @t t1
where t1.ID not in (select ID from @t where code2 = @exclude)
Don Simpson
January 14, 2015 at 1:21 pm
It would be nice to know 1) the actual requirement, 2) which solution is being used, and 3) do you understand how it works?
January 15, 2015 at 3:53 pm
I think the OP may just want a general illustration , may not be solving anything in particular. Here is another method :
declare @t table
(
ID int
, code1 char
, code2 char
);
insert @t values
(1,'a','m')
,(1,'b','n')
,(1,'c','T')
,(2,'d','m')
,(2,'e','n')
,(2,'f','n')
,(3,'g','m')
,(3,'h','n')
,(3,'i','T');
WITH myCTE AS (
SELECTID, MAX(case when /*code1='T' or*/ code2='T' then 1 else 0 END) as existsChar
FROM@t
GroupBy ID
)
SELECT ID from myCTE WHERE ExistsChar=0;
----------------------------------------------------
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply