Click here to monitor SSC
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
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1811 Visits: 1694
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-120898
Adi Cohn-120898
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2224 Visits: 6079
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
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3398 Visits: 6899
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
Mr or Mrs. 500
Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)

Group: General Forum Members
Points: 536 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
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3398 Visits: 6899
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
Mr or Mrs. 500
Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)

Group: General Forum Members
Points: 536 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
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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