CODEWITHSUNDEEP
mysqlsql
Ahmed
Ahmed

Reputation: 25

Can I run order by with distinct?

I have a table named emails which looks like this:

customer_id email_template_id send_time
1 1 2021-01-10
2 1 2021-01-10
1 2 2021-02-10
3 1 2021-03-10
2 2 2021-03-10
1 3 2021-04-10

I want to order the data by email_template_id which will be given on the web page (can be 1,2,3...). Whenever a person clicks on sort by (template_id = 2), I want to retrieve the data order by template_id then by date, but still get the unique customer_id of course. The result should look something like this:

customer_id email_template_id send_time
1 1 2021-01-10
1 2 2021-02-10
1 3 2021-04-10
2 1 2021-01-10
2 2 2021-03-10
3 1 2021-03-10

I have tried this but I am getting duplicate rows.

select distinct customer_id as cust,email_template_id
from email_order 
ORDER by FIELD(email_template_id,3,1,2,4,5,6),send_time desc

I also tried to use group by, but it disturbs the order

select distinct customer_id as cust,email_template_id 
from email_order
group by customer_id
ORDER by FIELD(email_template_id,3,1,2,4,5,6),send_time desc

I am using MYSQL 4.7.5 version.

Upvotes: 0

Views: 121

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

Your expected output table implies the following query:

SELECT customer_id, email_template_id, send_time
FROM email_order 
ORDER BY customer_id, email_template_id;

Upvotes: 1

Related Questions