SQL How to remove duplicates within select query ?

Question

Hi, I have a table which looks like that:

As You see, there are some date duplicates, so how to select only one row for each date in that table?

the column 'id_from_other_table' is from INNER JOIN with the table above

Answer 1

Do you need any other information except the date? If not:

SELECT DISTINCT start_date FROM table;

Answer 2

There are multiple rows with the same date, but the time is different. Therefore, DISTINCT start_date will not work. What you need is: cast the start_date to a DATE (so the TIME part is gone), and then do a DISTINCT:

SELECT DISTINCT CAST(start_date AS DATE) FROM table;

Depending on what database you use, the type name for DATE is different.

Answer 3

You mention that there are date duplicates, but it appears they're quite unique down to the precision of seconds.

Can you clarify what precision of date you start considering dates duplicate - day, hour, minute?

In any case, you'll probably want to floor your datetime field. You didn't indicate which field is preferred when removing duplicates, so this query will prefer the last name in alphabetical order.

 SELECT MAX(owner_name), 
        --floored to the second
        dateadd(second,datediff(second,'2000-01-01',start_date),'2000-01-01') AS StartDate
 From   MyTable
 GROUP BY dateadd(second,datediff(second,'2000-01-01',start_date),'2000-01-01')

Answer 4

Select Distinct CAST(FLOOR( CAST(start_date AS FLOAT ) )AS DATETIME) from Table should do it