How to get DISTINCT of a SUBSTRING in MySQL

Question

I have a query,

SELECT t2.id, t1.image, SUBSTRING(t2.start_time,1,10) AS mytime, 
  t2.user 
FROM post_table t1 
INNER JOIN watchUserList t2 ON t1.id = t2.movie_id 
WHERE user = 'john@gmail.com' 
ORDER BY id DESC;

In this query I want to fetch DISTINCT of mytime. I tried DISTINCT(SUBSTRING(t2.start_time,1,10)) AS mytime and SUBSTRING(t2.start_time,1,10) AS DISTINCT(mytime). But both doesn't work. How to get Distinct of a Substring in MySQL. Is there any way?

Can you add some sample (input) data with desired output? Because currently it's unclear if you do not know that DISTINCT if a modifier of the [SELECT](https://dev.mysql.com/doc/refman/8.0/en/select.html), or that you are trying something different. — Luuk, Oct 22 '22 at 10:37
Why do you treat a datetime like a string? There are functions to get parts from a datetime, like `YEAR()`, `MONTH()`, `DAY()` or the date-part of a datetime via `DATE()` — Honk der Hase, Oct 22 '22 at 10:48
[DISTINCT is not a function.](https://stackoverflow.com/a/7250654/20860). It's a query modifier. You must write `DISTINCT` after the `SELECT` keyword, before all columns. The `DISTINCT` modifier applies to the whole row, not just one column. — Bill Karwin, Oct 22 '22 at 14:36

score 0 · Answer 1 · answered Oct 24 '22 at 01:49

The correct syntax is

SELECT DISTINCT t2.id, t1.image, SUBSTRING(t2.start_time,1,10) AS mytime, 
  t2.user 
FROM post_table t1 
INNER JOIN watchUserList t2 ON t1.id = t2.movie_id 
WHERE user = 'john@gmail.com' 
ORDER BY id DESC;

But note, that its distinct over all the fields in the field-list.

Distinct on Multiple Columns When we use MySQL Distinct on multiple columns, then the SELECT Statement writes the unique combination of multiple columns instead of unique individual records.

Distinct On Multiple Columns

How to get DISTINCT of a SUBSTRING in MySQL

1 Answers1