Access database query is not duplicate records-CodePudding

The database table structure is as follows:

ID (on the) SampleTag Time

===========================================
1 B20121583 2020-12-20
2 B20121583 2020-12-20
3 B20121583 2020-12-20
4 BY the 2020-12-20
5 SHL 2020-12-20
6 SHL 2020-12-20
7 LB20121670 2020-12-21
8 LB20121670 2020-12-21
9 S20121670 2020-12-21
10 B20121578 2020-12-21
11 B20121562 2020-12-21
12 B20121562 2020-12-21

SQL statements to query filter SampleTag field the same record?

CodePudding user response:

Group by?

CodePudding user response:

 
Select * 
The from TB a 
Where not the exists (select 1 from TB b where a.S ampleTag=b.S ampleTag and Anderson, d & lt; B.i d)

Host the first thing you need to clear the same SampleTag rows is by what rules apply in a single row, and then according to this rule write a SQL statement

CodePudding user response:

 - method one: 
SELECT DISTINCT SampleTag, Time FROM TABLE 

- method 2: filter SampleTag duplicate data, take the last date data 
SELECT * FROM (SELECT ROW_NUMBER () OVER (PARTITION BY SampleTag ORDER BY Time DESC) AS NO 
FROM the TABLE) A 
WHERE A.N O=1

CodePudding user response:

ID (on the) SampleTag Time

===========================================
1 B20121583 2020-12-20
4 BY the 2020-12-20
5 SHL 2020-12-20
7 LB20121670 2020-12-21
9 S20121670 2020-12-21
10 B20121578 2020-12-21
11 B20121562 2020-12-21

Hope the query filters out SampleTag field duplicate records!

CodePudding user response:

The

reference 4 floor yuwentao4761901 reply:

ID (on the) SampleTag Time

===========================================
1 B20121583 2020-12-20
BY the 2020-12-20
23 SHL 2020-12-20
4 LB20121670 2020-12-21
5 S20121670 2020-12-21
6 B20121578 2020-12-21
7 B20121562 2020-12-21

Show that the Test, hope to get the results of a query for SampleTag not duplicate records

The said not enough clear, there is two lines SampleTag BY, for example, a line of Time is the 2020-12-21 a line of Time is 2020-12-21
Keep what? Which line filter? Or have to filter out?

CodePudding user response:

By date (every day), filtering SampleTag field duplicate records

CodePudding user response:

When the only if the sampletag and time and repeat filter? If there's a by in 2020-12-21, the 2020-12-22 is a mixture of, is all right
SELECT MIN (ID), SAMPLETAG, TIME FROM the TEST A GROUP BY SAMPLETAG, TIME

CodePudding user response:

 
The CREATE TABLE # A 
(
ID INT, 
SAMPLETAG VARCHAR (200), 
[TIME] DATE 
) 
# INSERT INTO A VALUES (1, 'B20121583', '2020-12-20') 
# INSERT INTO A VALUES (2, 'B20121583', '2020-12-20') 
# INSERT INTO A VALUES (3, 'B20121583', '2020-12-20') 
# INSERT INTO A VALUES (4, 'BY', '2020-12-20') 
# INSERT INTO A VALUES (5, 'SHL', '2020-12-20') 
# INSERT INTO A VALUES (6, 'SHL', '2020-12-20') 
# INSERT INTO A VALUES (7, 'LB20121670', '2020-12-21') 
# INSERT INTO A VALUES (8 'LB20121670', '2020-12-21') 
# INSERT INTO A VALUES (9, 'S20121670', '2020-12-21') 
# INSERT INTO A VALUES (10, 'B20121578', '2020-12-21') 
# INSERT INTO A VALUES (11, 'B20121562', '2020-12-21') 
# INSERT INTO A VALUES (12 'B20121562', '2020-12-21') 

The SELECT MIN (ID), SAMPLETAG, TIME FROM # aa GROUP BY SAMPLETAG, [TIME] ORDER BY MIN (ID) 

DROP TABLE # A

CodePudding user response:

Kun, 9/f,

reference jins kao kun jins kao response:

 
The CREATE TABLE # A 
(
ID INT, 
SAMPLETAG VARCHAR (200), 
[TIME] DATE 
) 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull