Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Conditional Where or Having Clause Expand / Collapse
Author
Message
Posted Wednesday, April 8, 2009 12:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 22, 2011 10:51 AM
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?
Post #693476
Posted Wednesday, April 8, 2009 1:08 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:42 AM
Points: 1,800, Visits: 1,555
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').
Post #693482
Posted Friday, April 10, 2009 2:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, December 1, 2014 7:30 AM
Points: 2,128, Visits: 5,565
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/
Post #694658
Posted Friday, April 10, 2009 2:51 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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? -- Stephen Stills
Post #695075
Posted Saturday, April 11, 2009 3:00 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 9, 2011 2:49 AM
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"
Post #695142
Posted Saturday, April 11, 2009 7:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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? -- Stephen Stills
Post #695179
Posted Sunday, April 12, 2009 10:29 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 9, 2011 2:49 AM
Points: 536, Visits: 412
ohh it was totaly my mistake... pardon for that..
the code is working...


"Don't limit your challenges, challenge your limits"
Post #695546
Posted Monday, April 13, 2009 6:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 22, 2012 1:17 PM
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
)

Post #695693
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse