April 8, 2009 at 12:58 pm
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?
April 8, 2009 at 1:08 pm
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').
April 10, 2009 at 2:16 am
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/
April 10, 2009 at 2:51 pm
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
April 11, 2009 at 3:00 am
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"
April 11, 2009 at 7:18 am
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
April 12, 2009 at 10:29 pm
ohh it was totaly my mistake... pardon for that.. 😀
the code is working... 🙂
"Don't limit your challenges, challenge your limits"
April 13, 2009 at 6:34 am
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 *
fromtemp
wherestatus in
(
selectstatus
fromtemp
group by status
having count(status)>1
)
and state is not null
union
select*
fromtemp
wherestatus in
(
selectstatus
fromtemp
groupby status
having count(status)=1
)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply