CODEWITHSUNDEEP
mysqlsql
Clyde Barrow
Clyde Barrow

Reputation: 2102

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 ?

Upvotes: 1

Views: 56

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Ues pivoting logic to turn out the birthday for each matching customer group:

SELECT customer_id
FROM customers_facts
WHERE (fact_name, fact_value) IN (('name', 'Milton'), ('city', 'Milan'))
GROUP BY customer_id 
HAVING MIN(fact_name) <> MAX(fact_name)
ORDER BY MAX(CASE WHEN fact_name = 'birthday' THEN fact_value END);

Other than the ORDER BY clause, I used a HAVING clause which ensures that both the matching name and city were present in each matching customer group.

Edit:

Here is your desired ORDER BY clause:

ORDER BY
    MAX(CASE WHEN fact_name = 'birthday' THEN fact_value END) DESC,
    MAX(CASE WHEN fact_name = 'company' THEN fact_value END);  -- ASC is default

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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)

Upvotes: 1

Related Questions