Question 113412 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL DISTINCT date only returning 1 value for particular table. Same query works on other tables

I have 2 tables with the exact same structure:

date (date)
title (varchar 275)

Both tables have rows for dates including 2021-01-01 to 2021-12-31

If I run this query on table1:

SELECT DISTINCT date FROM table1 WHERE date IN ('2021-01-02', '2021-01-04')

it returns the expected values of:

2021-01-02
2021-01-04

However, if I run it on table2 I only get 1 result which is the earliest date in the range. For example if I run the same query I only get 2021-01-02. If I change the query to:

SELECT DISTINCT date FROM table2 WHERE date IN ('2021-01-04', '2021-01-06')

I only get 2021-04-04. If I change the dates to ‘2021-01-06’ and ‘2021-01-07’ I only get 2021-01-06, etc, etc.

If I run the exact same query on table1, it returns all expected values. I assume it must be a difference in the tables but they have the exact same fields, field types, db collation, etc. It behaves as if there is a LIMIT 1.

EDIT @Joseph:
If I remove the DISTINCT and run the following query:

SELECT date FROM table2 WHERE date IN ('2021-01-04', '2021-01-06')

it returns the correct dates(duplicated):

2021-01-04
2021-01-04
2021-01-04
...
2021-01-06
2021-01-06

Adding the DISTINCT back in results in(only for table2):

2021-01-04
Tags:

2

Answers


  1. 0

    For some reason, you need to convert your date column to the actual date so you could do that like this

    SELECT DISTINCT date FROM table2 WHERE Date(date) IN ('2021-01-04', '2021-01-06')
    Login or Signup to reply.
  2. 0

    if your date column is string(varchar) data type.

    Try this

    SELECT district date 
FROM table1 
WHERE 
   STR_TO_DATE(date, '%m/%d/%Y') BETWEEN CAST('2021-01-04' AS DATE) AND CAST('2021-01-06' AS DATE)

    or
    if your date column is date data type.

    Try this

    SELECT district date 
FROM table1 
WHERE 
   date BETWEEN CAST('2021-01-04' AS DATE) AND CAST('2021-01-06' AS DATE)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search