Mysql – how to sort results by values in columns?

I have two database tables customers which contains data about customers with the scheme like that:

mysql> SELECT * FROM customers;

customer_id created_at              partner_id
1           "2019-08-20 09:17:58"   cats
2           "2019-09-12 11:46:37"   dogs

and customers_facts which keeps the customers facts in a form of fact_name and corresponding fact_value.

mysql> SELECT * FROM customers_facts;

customer_id fact_name   fact_value
1           name        Milton
1           city        Milan
1           birthday    "2019-08-20 09:17:58"
1           company     Idaho
2           surname     Bloom
2           name        Orlando
3           name        Milton
3           city        Milan
3           birthday    "2011-10-20 11:17:58"
3           company     Chicago

I want to create a query to get all customer_id where name=Milton and city=Milan sorted by birthday and company. So in my example the results would be:

mysql> SELECT customer_id FROM ....

customer_id
1             
3

I have a query which gets all the customers_id where name=Milton and city=Milan

SELECT cf.* FROM customers_facts cf 
WHERE cf.customer_id IN (
SELECT cf.customer_id FROM customers_facts cf
WHERE (cf.fact_name,cf.fact_value) IN (('name','Milton'),('city','Milan'))
GROUP BY cf.customer_id 
HAVING COUNT(*) = 2
)

But I have no idea on how to sort the results by fact_value How to do it ? Is it even possible with such scheme ?

Answer

This is a little tricky. You can’t filter easily before aggregating. So, do the filtering in the having clause:

SELECT customer_id
FROM customers_facts
GROUP BY customer_id 
HAVING SUM( fact_name = 'name' AND fact_value = 'Milton' ) > 0 AND
       SUM( fact_name = 'city' AND fact_value = 'Milan' ) > 0
ORDER BY MAX(CASE WHEN fact_name = 'birthday' THEN fact_value END) DESC,
         MAX(CASE WHEN fact_name = 'company' THEN fact_value END)

Advertisement

Answer