Conditional Where or Having Clause

  • 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?

  • 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').

  • 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/

  • 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

  • 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"

  • 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

  • ohh it was totaly my mistake... pardon for that.. 😀

    the code is working... 🙂

    "Don't limit your challenges, challenge your limits"

  • 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