Remove ALL duplicates if columns are the same in MYSQL select query

Question

I currently use the following to pull data for today from my database:

select TheTime, Thename, theplace from Stats where Selection = "SIR" and Thedata > 19.99 and DATE(TheTime)  = CURDATE()

The field TheTime is a DateTime field. I would like to check if any duplicate times are in this field. If there are then remove them all from the select query.

I am only looking for results that have 1 distinct DateTime. I have tried to use select DISTINCT TheTime but it still pulled the non-distanct values.

Selection = "SIR" Make a habit out of it writing strings values in SQL with single quotes.. in the ANSI/ISO SQL standard double quotes means identifiers meaning columns and or table names.. — Raymond Nijland
– Raymond Nijland, Commented Jul 25, 2019 at 14:42

IgorM · Accepted Answer · 2019-07-25 14:35:49Z

1

I have tried the following query on my database:

SELECT DISTINCT createdOn FROM tbldata

The createdOn is DATETIME data element with CURRENT_TIMESTAMP default expression. The query returns distinct values. My server version is 8.0.15, InnoDB engine

Be advised that

In MariaDB, the DISTINCT clause doesn't ignore NULL values. So when using the DISTINCT clause in your SQL statement, your result set will include NULL as a distinct value.

https://www.techonthenet.com/mariadb/distinct.php

answered Jul 25, 2019 at 14:35

IgorM

1,3413 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Raymond Nijland Over a year ago

"In MariaDB, the DISTINCT clause doesn't ignore NULL values. So when using the DISTINCT clause in your SQL statement, your result set will include NULL as a distinct value." Wierd statement i think we found our new w3fools as it sounds like DISTINCT in ANSI/ISO SQL standards is designed to ignore NULL values..

Pavel Vydra · Accepted Answer · 2019-07-25 14:28:46Z

0

Try GROUP BY TheTime.

SELECT DISTINCT is working with keys so you would have to use TheTime as key to make it work.

answered Jul 25, 2019 at 14:28

Pavel Vydra

112 bronze badges

1 Comment

Jarratt Perkins Over a year ago

GROUP BY TheTime will still give me 1 result depeding on order by would it not?

Collectives™ on Stack Overflow

Remove ALL duplicates if columns are the same in MYSQL select query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related