CODEWITHSUNDEEP
sql-servert-sql
user107242
user107242

Reputation: 59

Select only if more than X occurrences

My data is something like this:

Client Number | Order Date | Order Amount | Sequence (created with Row_Number())

I have created a sequence with Row_Number(), so I can see how many orders a client has.

If I use WHERE Sequence > 3, I lose the orders prior to 3. I can't use HAVING because I need to see every orders. How can I select the Client Numbers with more than 3 orders?

I would like to see:

Client Number | Order Date | Order Amount | Sequence
1111             Jan 01      100             1
1111             Jan 02      100             2
1111             Jan 03      100             3
1112             Jan 01      100             1
1112             ...         ...             ...
1112             Jan 20      100             20

So only those with Sequence above 3, while still keeping the line with sequence 1 and 2.

Upvotes: 1

Views: 64

Answers (2)

Shishir
Shishir

Reputation: 89

create table #test(clientnumber int, orderdate datetime, orderamount int)

insert into #test values
(1110, '01/01/2016', 100),
(1110, '01/02/2016', 100),
(1111, '01/01/2016', 100),
(1111, '01/02/2016', 100),
(1111, '01/03/2016', 100),
(1112, '01/01/2016', 100),
(1112, '01/02/2016', 100),
(1112, '01/03/2016', 100),
(1112, '01/04/2016', 100);

with cte as(
select clientnumber, orderdate, orderamount,
count(*) over(partition by clientnumber ) as ran
from #test)
select * from cte
where ran >= 3

Upvotes: 0

Teja
Teja

Reputation: 13534

SELECT *
  FROM data
 WHERE ClientNumber IN 
 (
   SELECT ClientNumber
     FROM data
   GROUP BY ClientNumber
   HAVING COUNT(1) >= 3
 );

Upvotes: 2

Related Questions