CODEWITHSUNDEEP
sqlms-access
Req_7
Req_7

Reputation: 85

Remove duplicate rows in MS-Access

I am using Microsoft Access and in it, I have a table with data that is sometimes repeated. I'm not able to create an SQL query that removes duplicate data, leaving only distinct data in the table. Can someone help me?

My current table:

 Date        | Level  |  Name
---------+--------+--------
 12/25/2021  |   2    | Jack
 12/25/2021  |   2    | Jack
 12/10/2021  |   3    | Ana
 12/01/2021  |   1    | Lenon
 12/01/2021  |   1    | Lenon
 12/30/2021  |   3    | Ana

Expected result:

Date         | Level  |  Name
---------+--------+--------
 12/25/2021  |   2    | Jack
 12/10/2021  |   3    | Ana
 12/01/2021  |   1    | Lenon
 12/30/2021  |   3    | Ana

PS: Ana appears twice in the expected result table because the dates of the two rows referring to Ana are different, so they are not duplicated values.

Upvotes: 0

Views: 2877

Answers (2)

Sirch Dcmp
Sirch Dcmp

Reputation: 147

You can do a group by of the Date, Level and Name columns.

Use this query:

SELECT  Date
       ,Level
       ,Name
FROM   <TableName>
GROUP BY Date, Level, Name

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Just use select distinct:

select distinct t.*
from t;

I would add that tables should not have duplicate rows. Something is wrong with the table generation if you are getting duplicates -- either the query being used or the process for inserting rows into the table.

Upvotes: 2

Related Questions