We help IT Professionals succeed at work.

Need a query to pull only 30 items

Cole100
Cole100 asked
on
I have built a very simple query to pull customer email address' from the previous week for each of my stores. What I need to do is have only 30 email address' pulled for each store rather than all of them from the week. Any idea's how I would do that?
SELECT dbo_Invoice_Tb.Store_Number, dbo_Invoice_Tb.Customer_Email_Address
FROM dbo_Invoice_Tb
WHERE (((dbo_Invoice_Tb.Customer_Email_Address) Like "*@*") AND ((dbo_Invoice_Tb.Invoice_Date) Between Date()-7 And Date()-1))
ORDER BY dbo_Invoice_Tb.Store_Number, dbo_Invoice_Tb.Customer_Email_Address DESC;

Open in new window

Comment
Watch Question

Commented:
Select Top 30 ...
Cole100IT Systems Manager

Author

Commented:
I have no idea where to define that at...

Commented:
I don't think that's what you need. That will only pull the first 30 items total, not 30 from each store. That is what you want, right? 30 from each store?

Lee
Cole100IT Systems Manager

Author

Commented:
Correct. 30 from each store

Commented:
Try this:

SELECT a.Store_Number, a.Customer_Email_Address
FROM dbo_Invoice_Tb a
WHERE a.Customer_Email_Address IN (
SELECT Top 30 b.Customer_Email_Address FROM dbo_Invoice_Tb b
WHERE b.Store_Number = a.Store_Number)
ORDER BY a.Store_Number, a.Customer_Email_Address ASC;

Commented:
Here's how to do it for one store:

SELECT Top 30 dbo_Invoice_Tb.Customer_Email_Address
FROM dbo_Invoice_Tb
WHERE (((dbo_Invoice_Tb.Customer_Email_Address) Like "*@*") AND ((dbo_Invoice_Tb.Invoice_Date) Between Date()-7 And Date()-1))
AND dbo_Invoice_Tb.Store_Number = 1234
ORDER BY dbo_Invoice_Tb.Customer_Email_Address DESC;


... You'll have to replace 1234 with a valid store number.
Cole100IT Systems Manager

Author

Commented:
GRayL - The query runs but does not return data and does not time out. I tried it twice and aborted after 5 minutes

PDEBAETS - It works great for 1 store but I need it to do it for all 34 stores in one query

Commented:
You may need a VBA function or subroutine to do what you want. What is it you are after? What is your ultimate goal here? It may be easier to address the full scope of what you are doing rather than this small piece.
Cole100IT Systems Manager

Author

Commented:
I want to send 30 random customers per week per store an email to fill out a survey about there recent visit at that store. We capture about 50% of our customers email addresses so using them all would be more potential responses than what we want right now.

What I am trying to do is create a query that would give me these email address' which I could than export into an excel file which the mass email software we use can import the data from.

Commented:
Try this.

My Google query: sql top from group

Copied and modified from first comment here http://www.bennadel.com/blog/1114-Selecting-Top-X-From-Each-Group.htm
SELECT tI3.Store_Number, tI3.Customer_Email_Address, qI.ranknum
FROM dbo_Invoice_Tb AS tI3 INNER JOIN
  (SELECT tI1.id, COUNT(*) AS ranknum
   FROM dbo_Invoice_Tb AS tI1 INNER JOIN dbo_Invoice_Tb AS tI2 ON tI1.id = tI2.id
   WHERE tI1.Customer_Email_Address Like "*@*" AND tI1.Invoice_Date) Between Date()-7 And Date()-1
   GROUP BY tI1.id HAVING COUNT(*) <= 3) AS qI)
  ON tI3.id = qI.id
ORDER BY tI3.Store_Number, tI3.Customer_Email_Address DESC;

Open in new window

Cole100IT Systems Manager

Author

Commented:
LEE555J5 - I am getting a "Syntax error in FROM clause."

Commented:
Remove the ) after the qI
Cole100IT Systems Manager

Author

Commented:
Removed the ) and I am still getting the Syntax error

Commented:
In the subquery, remove the ) before Between. I tried to delete all those () Access adds in. They make it very cluttered and confusing.

To make sure the subquery works, create a new query, switch to SQL View, paste in the subquery, and try to run it.

Commented:
Also, does your table dbo_Invoice_Tb have an field named "id"?

Change the statement in the subquery to

ON tI1.your_table_id_field = tI2.your_table_id_field
"id" was in the example I found and modified.
Cole100IT Systems Manager

Author

Commented:
You have lost me now. I am a click and drag access user so the SQL is a bit confusing. I do not have an ID field in this table. Here is a screenshot of the table screenshot
Commented:
This work any better?

SELECT a.Store_Number, a.Customer_Email_Address
FROM dbo_Invoice_Tb a
WHERE a.Customer_Email_Address IN (
SELECT Top 30 b.Customer_Email_Address FROM dbo_Invoice_Tb b
WHERE b.Store_Number = a.Store_Number AND b.Invoice_Date BETWEEN Date()-7 AND Date()-1
ORDER BY Rnd(), b.Email_Address)
ORDER BY a.Store_Number;

Commented:
Thanks glad to help.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.