CODEWITHSUNDEEP
sql-serverstored-procedures
daniel aagentah
daniel aagentah

Reputation: 1702

SQL Select Records ONLY When a Column Value Is In More Than Once

I have a stored procedure in SQL Server, I am trying to select only the records where a column's value is in there more than once, This may seem a bit of an odd request but I can't seem to figure it out, I have tried using HAVING clauses but had no luck..

I want to be able to only select records that have the ACCOUNT in there more than once, So for example:

ACCOUNT | PAYDATE
-------------------
B066    | 15
B066    | OUTSTAND
B027    | OUTSTAND  <--- **SHOULD NOT BE IN THE SELECT**
B039    | 09
B039    | OUTSTAND
B052    | 09
B052    | 15
B052    | OUTSTAND

BO27 should NOT show in my select, and the rest of the ACCOUNTS should.

here is my start and end of the Stored Procedure:

Select * from (

*** SELECTS ARE HERE ***

                ) X where O_STAND <> 0.0000 
        group by X.ACCOUNT, X.ACCT_NAME , X.DAYS_CR, X.PAYDATE, X.O_STAND 
        order by X.ACCOUNT

I have been struggling with this for a while, any help or advice would be appreciated. Thank you in advance.

Upvotes: 0

Views: 115

Answers (2)

Roger Wolf
Roger Wolf

Reputation: 7692

Yes, the having clause is for solving exactly this kind of tasks. Basically, it's like where, but allows to filter not only by column values, but also by aggregate functions' results:

declare @t table (
    Id int identity(1,1) primary key,
    AccountId varchar(20)
);

insert into @t (AccountId)
values
    ('B001'),
    ('B002'),
    ('B015'),
    ('B015'),
    ('B002');

-- Get all rows for which AccountId value is encountered more than once in the table
select *
from @t t
where exists (
    select 0
    from @t h
    where h.AccountId = t.AccountId
    group by h.AccountId
    having count(h.AccountId) > 1
);

Upvotes: 0

Dzmitry Paliakou
Dzmitry Paliakou

Reputation: 1627

you could replace the first string with

Select *, COUNT(*) OVER (PARTITION BY ACCOUNT) cnt FROM (

and then wrap your query as subquery once more

SELECT cols FROM ( query ) q WHERE cnt>1

Upvotes: 1

Related Questions