SQL How to remove duplicates within select query?

Question

I have a table which looks like that:

alt text

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

Hello Tony, I found this to be useful when I was trying to do a similar thing. It does not answer your question and remove the duplicates but it helps give a count which might be relevant: COUNT(*) - COUNT(DISTINCT name) AS 'duplicate name' — JPK, May 04 '16 at 10:27

score 22 · Answer 1 · answered Sep 12 '10 at 15:38

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.

score 10 · Answer 2 · answered Sep 12 '10 at 15:31

10

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

SELECT DISTINCT start_date FROM table;

answered Sep 12 '10 at 15:31

amorfis

15,390
15
77
125

2

The dates are the same, but the times are different; as such, `DISTINCT` won't work on `start_date` without some formatting – LittleBobbyTables - Au Revoir Sep 12 '10 at 15:39

score 5 · Accepted Answer · edited May 23 '17 at 11:47

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')

I consider year, month, date, and hh:mm:ss – Tony Sep 12 '10 at 15:48 — Tony, Sep 12 '10 at 15:48

score 3 · Answer 4 · edited Dec 18 '11 at 14:29

3

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

edited Dec 18 '11 at 14:29

answered Sep 22 '10 at 14:06

ADB

31
1

score 2 · Answer 5 · answered Oct 23 '12 at 09:11

2

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

answered Oct 23 '12 at 09:11

Akshay

1,831
1
18
22

score 2 · Answer 6 · edited Oct 22 '14 at 06:40

2

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

edited Oct 22 '14 at 06:40

zessx

68,042
28
135
158

answered Oct 22 '14 at 06:23

Dharmesh Porwal

1,406
2
12
21

hi @zessx how you edited it ,i put the formatted code as you edited but after posting,it did't appear as it is now ,how u do it ??? – Dharmesh Porwal Oct 27 '14 at 06:32
1

Pasting formatted code is not enough (in fact, it does nothing). You need to paste your code, select it and use `Ctrl+K` (or click on the `{}` button in the StackOverflow editor). You also can do it manually, adding 4 spaces before each line of your code. – zessx Oct 27 '14 at 06:56

score 0 · Answer 7 · answered Jul 12 '17 at 01:01

0

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.

answered Jul 12 '17 at 01:01

M T Head

1,085
9
13

SQL How to remove duplicates within select query?

7 Answers7

Linked