mysql min 日期,MySQL SELECT MIN一直有效，但仅在日期之间返回

I can certainly do this by iterating through results with PHP, but just wanted to know if someone had a second to post a better solution.

The scenario is that I have a list of transactions. I select two dates and run a report to get the transactions between those two dates...easy. For one of the reporting sections though, I need to only return the transaction if it was their first transaction.

Here was where I got with the query:

SELECT *, MIN(bb_transactions.trans_tran_date) AS temp_first_time

FROM

bb_business

RIGHT JOIN bb_transactions ON bb_transactions.trans_store_id = bb_business.store_id

LEFT JOIN bb_member ON bb_member.member_id = bb_transactions.trans_member_id

WHERE

bb_transactions.trans_tran_date BETWEEN '2010-08-01' AND '2010-09-13'

AND bb_business.id = '5651'

GROUP BY bb_member.member_id

ORDER BY bb_member.member_id DESC

This gives me the MIN of the transactions between the selected dates. What I really need is the overall MIN if it falls between the two dates. Does that make sense?

I basically need to know if a customers purchased for the first time in the reporting period.

Anyways, no huge rush as I can solve with PHP. Mostly for my own curiosity and learning.

Thanks for spreading the knowledge!

EDIT:

I had to edit the query because I had left one of my trial-errors in there. I had also tried to use the temporary column created from MIN as the selector between the two dates. That returned an error.

SOLUTION:

Here is the revised query after help from you guys:

SELECT * FROM (

SELECT

bb_member.member_id,

MIN(bb_transactions.trans_tran_date) AS first_time

FROM

bb_business

RIGHT JOIN bb_transactions ON bb_transactions.trans_store_id = bb_business.store_id

LEFT JOIN bb_member ON bb_member.member_id = bb_transactions.trans_member_id

WHERE bb_business.id = '5651'

GROUP BY bb_member.member_id

) AS T

WHERE T.first_time BETWEEN '2010-08-01' AND '2010-09-13'

解决方案

If we do a minimum of all transactions by customer, then check to see if that is in the correct period we get something along the lines of...

This will simply give you a yes/no flag as to whether the customer's first purchase was within the period...

SELECT CASE COUNT(*) WHEN 0 THEN 'Yes' ELSE 'No' END As [WasFirstTransInThisPeriod?]

FROM (

SELECT bb_member.member_id As [member_id], MIN(bb_transactions.trans_tran_date) AS temp_first_time

FROM bb_business

RIGHT JOIN bb_transactions ON bb_transactions.trans_store_id = bb_business.store_id

LEFT JOIN bb_member ON bb_member.member_id = bb_transactions.trans_member_id

WHERE bb_business.id = '5651'

GROUP BY bb_member.member_id

) T

WHERE T.temp_first_time BETWEEN '2010-08-01' AND '2010-09-13'

ORDER BY T.member_id DESC

(this is in T-SQL, but should hopefully give an idea of how this can be achieved similarly in mySQL)

Simon