Getting SQL Results That Are Distinct Across Two Columns

in

So this is a weird issue I just came across. Here's an example table schema:

mysql> describe queues;
+--------------+---------------+
| Field        | Type          |
+--------------+---------------+
| id           | int(11)       |
| customer_id  | mediumint(9)  |
| request_time | decimal(12,0) |
| item_id      | smallint(6)   |
+--------------+---------------+

mysql> select * from queues;
+------+--------------+--------------+--------+
| id   | customer_id | request_time | item_id |
+------+-------------+--------------+---------+
| 6829 |       15066 | 201704161118 |       1 |
| 6872 |       15066 | 201704161118 |       2 |
| 6875 |       15066 | 201704161118 |      26 |
| 6880 |       15066 | 201704161118 |       8 |
| 6881 |       15066 | 201704161118 |      15 |
| 6930 |       15077 | 201704161942 |       6 |
| 8683 |       14625 | 201704171412 |      10 |
+------+-------------+--------------+---------+

In my example, I might have the same customer requesting multiple items at the same time. I want to display all the items they have requested in the same line. That means I want to get a list of all the unique customers and request times combined. Yes, this isn't the *greatest* example because this table should probably be designed in a different way, but stick with me!

If I only want customer_id and request_time, that is pretty simple.

mysql> SELECT DISTINCT customer_id, request_time FROM queues;
+-------------+--------------+
| customer_id | request_time |
+-------------+--------------+
|       15066 | 201704161118 |
|       15077 | 201704161942 |
|       14625 | 201704171412 |
+-------------+--------------+

However, in my case, I need the queue id to do additional queries. That's where it gets just a smidge bit more complicated! Instead of just a simple DISTINCT, I've got to count the distinct records and then use HAVING to actually limit it.

mysql> SELECT *, 
COUNT(DISTINCT customer_id, request_time) as unique_orders 
FROM queues 
GROUP BY customer_id, request_time 
HAVING unique_orders >= 1;
+------+-------------+--------------+---------+
| id   | customer_id | request_time | item_id |
+------+-------------+--------------+---------+
| 6829 |       15066 | 201704161118 |       1 |
| 6930 |       15077 | 201704161942 |       6 |
| 8683 |       14625 | 201704171412 |      10 |
+------+-------------+--------------+---------+

Not too difficult, but I did go through a few different variations before getting to this result. I wanted it to work, but SELECT id, DISTINCT(customer_id, request_time) definitely does not!