SQL How to remove duplicates within select query

Question

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

User · Answer

If you want to select any random single row for particular day, then

SELECT * FROM table_name GROUP BY DAY(start_date)

If you want to select single entry for each user per day, then

SELECT * FROM table_name GROUP BY DAY(start_date),owner_name

User · Answer

Select Distinct CAST FLOOR  CAST start date AS FLOAT    AS DATETIME  from Table

User · Answer

You have to convert the  DateTime  to a  Date    Then you can easier select just one for the given date no matter the time for that date

User · Answer

Do you need any other information except the date  If not   SELECT DISTINCT start date FROM table

User · Answer

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

User · Answer

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

User · Answer

here is the solution for your query returning only one row for each date in that table here in the solution  tony  will occur twice as two different start dates are there for it   SELECT   FROM        SELECT T1    ROW NUMBER   OVER PARTITION BY TRUNC START DATE  OWNER NAME ORDER BY 1 2 DESC    RNM     FROM TABLE T1   WHERE RNM 1

[sql] SQL How to remove duplicates within select query?

Examples related to sql