Why is MySQL it outputting data from, effectively, two different queries?

Question

Gday All,

I am trying to get the details of the first ever transaction for a given customer within a period of time.

Consider the following:

SELECT MIN(t.transaction_date_start), t.*
FROM transactions t
WHERE t.customer_id IN (1,2,3)
AND t.transaction_date_finished >= '2010-02-01 00:00:00'
AND t.transaction_date_finished <= '2010-02-28 23:59:59'
GROUP BY t.customer_id

The above SQL outputs the minimum transaction date correctly however the rest of the data is not what I expect.

It is populated with data of the first grouped customer id not the minimum.

Why is MySQL it outputting data from, effectively, two different queries?

How do I fix my SQL so it selects all the details for the first transaction?

Cheers,

Michael

Answer 1

can order by instead of using group:

select * from transactions where customer_id = 1 and
  transaction_date_finished between '2010-02-01 00:00:00' and '2010-02-28 23:59:59'
  order by transaction_date_finished limit 1

Answer 2

I am trying to get the details of the first ever transaction for a given customer within a period of time.

Here you go:

SELECT TOP 1 *
FROM transactions t
WHERE t.customer_id = 1
AND t.transaction_date_finished >= '2010-02-01 00:00:00'
AND t.transaction_date_finished <= '2010-02-28 23:59:59'
ORDER BY t.transaction_date_start

Answer 3

Welcome to MySQL's "hidden column" in GROUP BY "feature" - it's documented here. Standard SQL doesn't allow you to define a GROUP BY clause that does not include columns that are not wrapped in aggregate functions (MIN, MAX, COUNT, etc) in the SELECT clause:

SELECT MIN(t.transaction_date_start),  -- (a) Wrapped in an aggregate function
       t.*                             -- (b) These are not
  FROM transactions t
 WHERE ...
GROUP BY t.customer_id                 -- (c) Only customer_id - refer to (b)

To get the row(s) matching the min date, use:

SELECT t.*
  FROM TRANSACTIONS t
  JOIN (SELECT ta.customer_id,
               MIN(ta.transaction_date_start) AS min_date
          FROM TRANSACTIONS ta
         WHERE ta.transaction_date_finished BETWEEN '2010-02-28 23:59:59' AND '2010-02-01 00:00:00'
      GROUP BY ta.customer_id) x ON x.customer_id = t.customer_id
                                AND x.min_date = t.transaction_date_start
 WHERE t.customer_id IN (1, 2, 3)