CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
a coder
a coder

Reputation: 7639

SELECT newest record of any GROUP of records (ignoring records with one record)

Having trouble with a query to return the newest order of any grouped set of orders having more than 1 order. CREATE & INSERTs for the test data are below.

This query returns the unique customer id's I want to work with, along with the grouped order_id's. Of these records, I only need the most recent order (based on date_added).

SELECT COUNT(customer_id), customer_id, GROUP_CONCAT(order_id) FROM orderTable GROUP BY customer_id HAVING COUNT(customer_id)>1 LIMIT 10;

mysql> SELECT COUNT(customer_id), customer_id, GROUP_CONCAT(order_id) FROM orderTable     GROUP BY customer_id HAVING COUNT(customer_id)>1 LIMIT 10;
+--------------------+-------------+------------------------+
| COUNT(customer_id) | customer_id | GROUP_CONCAT(order_id) |
+--------------------+-------------+------------------------+
|                  2 | 0487        | F9,Z33                 |
|                  3 | 1234        | 3A,5A,88B              |
+--------------------+-------------+------------------------+
2 rows in set (0.00 sec)

I'm looking for order Z33 (customer_id 0487) and 3A (customer_id 1234).

For clarification, I do not want orders for customers that have only ordered once.

Any help or tips to get me pointed in the right direction appreciated.

Sample table data:

--

-- Table structure for table orderTable

CREATE TABLE IF NOT EXISTS orderTable ( customer_id varchar(10) NOT NULL, order_id varchar(4) NOT NULL, date_added date NOT NULL, PRIMARY KEY (customer_id,order_id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--

-- Dumping data for table orderTable

INSERT INTO orderTable (customer_id, order_id, date_added) VALUES ('1234', '5A', '1997-01-22'), ('1234', '88B', '1992-05-09'), ('0487', 'F9', '2002-01-23'), ('5799', 'A12F', '2007-01-23'), ('1234', '3A', '2009-01-22'), ('3333', '7FHS', '2009-01-22'), ('0487', 'Z33', '2004-06-23');

==========================================================

Upvotes: 0

Views: 275

Answers (1)

DRapp
DRapp

Reputation: 48139

Clarification of the query. The question was to only include those customers that had more... hence my query has it INSIDE with the GROUP BY... This way it ONLY GIVES the customer in question that HAD multiple orders, but at the same time, only gives the most recent date OF the last order for the person... Then the PreQuery is re-joined to the orders table by the common customer ID, but only for the order that matches the last date as detected in the prequery. If a customer only had a single order, its inner PreQuery count would have only been 1 and thus excluded from the final PreQuery result set.

select ot.*
   from
      ( select
            customer_id,
            max( date_added ) as LastOrderDate,
         from
            orderTable
         having 
            count(*) > 1
         group by
            customer_id ) PreQuery
     join orderTable ot
        on PreQuery.Customer_ID = ot.Customer_ID
       and PreQuery.LastOrderDate = ot.date_added

Upvotes: 1

Related Questions