3

this one is driving me to drink so I would love some help.

I've got a table with:

act_Address, act_OrderID, act_Date

I'm trying to get the first act_Date for each address we shipped to.

Here's what I've tried but it's been running now for well over an hour so I'm thinking this isn't going to work...

SELECT c.act_Address,

(SELECT o.act_OrderID 
FROM tbl_Activity o 
WHERE c.act_Address = o.act_Address 
ORDER BY o.act_Date 
LIMIT 1) AS order_id, 

(SELECT d.act_Date
FROM tbl_Activity d 
WHERE c.act_Address = d.act_Address 
ORDER BY d.act_Date 
LIMIT 1) as order_date

FROM tbl_Activity c

I've got to be doing something very wrong, doesn't seem like getting the first date for an address would be that hard, but I'm not that smart.

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
Kevin Phillips
  • 33
  • 1
  • 4

2 Answers2

1

Your query uses two correlated subqueries to get act_Date and act_OrderID values. Each subquery is executed once for every record of tbl_Activity.

You can use:

SELECT act_Address, MIN(act_Date) AS fist_Date
FROM tbl_Activity
GROUP BY act_Address

to get the first date per address. Then you can use the above query as a derived table and join back to the original table to get the rest of the fields:

SELECT t1.act_Address, t1.act_OrderID, t1.act_date
FROM tbl_Activity AS t1
JOIN (
   SELECT act_Address, MIN(act_Date) AS fist_Date
   FROM tbl_Activity
   GROUP BY act_Address
) AS t2 ON t1.act_Address = t2.act_Address AND t1.act_Date = t2.first_Date

I also propose placing a composite index on (act_Address, act_Date).

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
0

You can do this by GROUP BY in a subselect:

SELECT a.act_Address, a.act_OrderID, a.act_Date
FROM (
    SELECT a2.act_Address addr, MIN(a2.act_Date) mindate FROM tbl_Activity a2
    GROUP BY a2.act_Address
) g, tbl_Activity a
WHERE a.act_Address = g.addr AND a.act_Date = g.mindate;
Quetsalkoatl
  • 158
  • 12