SQL Select stament

Question

SQL Select stament

Dee Dee-422077

Ten Centuries

Points: 1082
More actions
January 9, 2015 at 10:09 pm

#300179

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,

Viewing 13 posts - 1 through 12 (of 12 total)

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

Eirikur Eiriksson SSC Guru Points: 182946 More actions · Answer 1

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

LinksUp SSCertifiable Points: 6526 More actions · Answer 2

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/

Jeff Moden SSC Guru Points: 1004581 More actions Follow · Answer 3

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004581 More actions Follow · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004581 More actions Follow · Answer 5

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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');

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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);

Eirikur Eiriksson SSC Guru Points: 182946 More actions · Answer 8

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;-)

Dee Dee-422077 Ten Centuries Points: 1082 More actions · Answer 9

Dee Dee-422077

Ten Centuries

Points: 1082

January 13, 2015 at 2:09 pm

#1769938

Thank you all, Your solutions work for me

Best,

Dee

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 10

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

I'm not sure about Heisenberg.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

It would be nice to know 1) the actual requirement, 2) which solution is being used, and 3) do you understand how it works?

MMartin1 One Orange Chip Points: 27834 More actions · Answer 12

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;

----------------------------------------------------

SQL Select stament

Cookies on SQLServerCentral