Last 3 orders of customers ordered more than 3 times 1

[cumap]

Hello,
My goal is:
search for all customers custID with MORE THAN 3 orders. AND then in the same tblOrderRecords, I'd like query to return the more recently 3 records of each of the customers from the first outputs.

I'm running this query but it returns all records:

SELECT orderID, dteDate FROM tblOrderRecords WHERE EXISTS (SELECT custID, COUNT(*) FROM tblOrderRecords WHERE status='1' GROUP BY custID HAVING COUNT(*) > 3) ORDER BY custID DESC

thanks

 

[cumap]

Nevermind, this works,

SELECT idCPBPS, idCustomer FROM tblCPBPS WHERE idCustomer IN (SELECT idCustomer FROM tblCPBPS WHERE quoteStatus='Sale Order' GROUP BY idCustomer HAVING COUNT(*) > 3) ORDER BY idCustomer DESC

However, it doesn't return the most recent 3 records of each customer. My option now is to run 2 queries separately with TOP 3 for each customer found with more than 3 records. UNLESS, someone can help me to include that requirement to the query.

Thanks!

 

[drlex]

If you're on SQL Server 2005, you could use row_number() to count the orders by Customer and sort by date to get the three latest.
Assuming that the date field is dteDATE in the table tblCPBPS:

[red][b]SELECT idCPBPS, idCustomer from
([/red][/b]
SELECT idCPBPS, idCustomer [red][b], row_number() over (partition by idCustomer order by dteDATE desc) as rownum[/red][/b] FROM tblCPBPS 
WHERE idCustomer IN (SELECT idCustomer FROM tblCPBPS WHERE quoteStatus='Sale Order' GROUP BY idCustomer HAVING COUNT(*) > 3) 
[red][b])T1
WHERE rownum <= 3
ORDER BY idCustomer 
[/red][/b]

Not Tested!
(c) B.Borissov

soi là, soi carré

 

[cumap]

Thank you B.Borissov, you rock!!!