SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Conditional Where or Having Clause


Conditional Where or Having Clause

Author
Message
gayakr
gayakr
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 60
Hi, Following is the structure of my table

Status State Name

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

Pending Nebraska ABC

Pending NULL XYZ

Active NULL PQR



Output required

Status State Name

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

Pending Nebraska ABC

Active NULL PQR



i.e, only if the count(Status)>1 and State is NULL , the row has to be omitted, else it should appear in the result. Any ideas?
Mayank Khatri
Mayank Khatri
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5402 Visits: 1783
so if for a particular State = NULL ,count(status)>1 only 'Active' status row should be displayed or any status row can be displayed('Active' or 'Pending').
Adi Cohn
Adi Cohn
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19530 Visits: 6653
You can do it with the row_number() function. Partition the data according to status column and order it by state column in descending order. Then you can take only the columns that state is null and row_number() function returned 1 or the rows that state column did not have null as a value in it regardless of the value of row_number() function.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
The Dixie Flatline
The Dixie Flatline
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28786 Visits: 6934
Will this work?


declare @sample table (Status varchar(10), State varchar(20), Name Varchar(10))
insert into @sample
select 'Pending', 'Nebraska', 'ABC' union all
select 'Pending', NULL, 'XYZ' union all
select 'Active', NULL, 'PQR'

select *
from @sample
where state is not null
or status not in (select status from @sample group by status having count(*) > 1)



__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Kruti Kansara
Kruti Kansara
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1730 Visits: 412
Will this work?

declare @sample table (Status varchar(10), State varchar(20), Name Varchar(10))
insert into @sample
select 'Pending', 'Nebraska', 'ABC' union all
select 'Pending', NULL, 'XYZ' union all
select 'Active', NULL, 'PQR'

select *
from @sample
where state is not null
or status not in (select status from @sample group by status having count(*) > 1)



It wont give you "Active NULL PQR" as your query filters records having state=Null

"Don't limit your challenges, challenge your limits"
The Dixie Flatline
The Dixie Flatline
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28786 Visits: 6934
You might try running the code before making pronouncements like that. ;-)
I'm looking at the following results on my screen right now.

Status State Name
Pending Nebraska ABC
Active NULL PQR

The WHERE clause dictates which records to include, and it contains an OR. So, if the state is not null, the row is included OR ,if there is only one row for that status, the row is included. Maybe it will make more sense to you this way. It produces identical results.


where
state is not null
OR
-- where
status in (select status from @sample group by status having count(*) = 1)



__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Kruti Kansara
Kruti Kansara
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1730 Visits: 412
ohh it was totaly my mistake... pardon for that.. :-D
the code is working... :-)

"Don't limit your challenges, challenge your limits"
sirajudeen.saleem
sirajudeen.saleem
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 20
Hi will this is helpful for u?

IF OBJECT_ID('temp') IS NOT NULL
DROP TABLE temp
go
CREATE TABLE temp
(
status varchar(20),
state varchar(20),
Name1 varchar(20)
)
insert into temp
select 'Pending', 'Delhi', 'ABC' union all
select 'Pending', NULL, 'XYZ' union all
select 'Active', NULL, 'PQR' union all
select 'Active', 'Mumbai', 'PQR' union all
select 'IAC', NULL, 'PQR'
go
select *
from temp
where status in
(
select status
from temp
group by status
having count(status)>1
)
and state is not null
union
select *
from temp
where status in
(
select status
from temp
group by status
having count(status)=1
)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search