Getting SQL Results That Are Distinct Across Two Columns
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!