Duplicate Data

Forum for DBF Commander discussing.

Duplicate Data

Postby RenatoReis » Mon Oct 08, 2018 9:22 pm

Hello.
Does anyone know if I can construct a query that selects records that have duplicate data in a given field?
Thank you
RenatoReis
 
Posts: 3
Joined: Wed Jan 04, 2017 3:56 pm

Re: Duplicate Data

Postby Admin » Tue Oct 09, 2018 2:17 pm

Hello Renato,
here is the query:
Code: Select all
select givenfield, count(givenfield) as dup_count
from table1
group by givenfield
having dup_count > 1
Best regards,
Oleg Zhechkov
User avatar
Admin
Site Admin
 
Posts: 124
Joined: Wed Apr 21, 2010 7:27 pm

Re: Duplicate Data

Postby RenatoReis » Wed Oct 10, 2018 8:47 pm

Thank you, your answer worked but I probably have not been so clear.
I need to identify records where the data is the same as Field_3, for data other than Field_1 and Field_2. Thus:
Field_1 Field_2 Field_3 Field_4 Field_5
XX 001 123 20.00 2018-10-01
ZZ 010 123 30.00 2018_02-11
XX 001 123 22.00 2018-03-12
YY 055 457 18.00 2018-03-25
GG 047 987 33.00 2018-05-13

Result this
Field_1 Field_2 Field_3 Field_4 Field_5
XX 001 123 20.00 2018-10-01
ZZ 010 123 30.00 2018_02-11
XX 001 123 22.00 2018-03-12

Thank you
RenatoReis
 
Posts: 3
Joined: Wed Jan 04, 2017 3:56 pm

Re: Duplicate Data

Postby Admin » Wed Oct 10, 2018 9:21 pm

Not sure that I've understood you right, but it looks like you need to wrap the above query with another JOIN:
Code: Select all
select t1.*
from
(
  select field_3, count(field_3) as dup_count
  from "D:\table1.dbf"
  group by field_3
  having dup_count > 1
) as t0
left join "D:\table1.dbf" as t1 on t0.field_3=t1.field_3
Best regards,
Oleg Zhechkov
User avatar
Admin
Site Admin
 
Posts: 124
Joined: Wed Apr 21, 2010 7:27 pm

Re: Duplicate Data

Postby RenatoReis » Fri Oct 12, 2018 9:06 pm

HY Oleg.
I need to identify the same value for the field_3 where field_1 and field_2 are diferentes. (field_1 and field_2 are ID Fields)
Thanks friend.
RenatoReis
 
Posts: 3
Joined: Wed Jan 04, 2017 3:56 pm

Re: Duplicate Data

Postby Admin » Fri Oct 12, 2018 9:57 pm

I need to identify the same value for the field_3 where field_1 and field_2 are diferentes

Thus, the result must be
Field_1 Field_2 Field_3 Field_4 Field_5
XX 001 123 20.00 2018-10-01
ZZ 010 123 30.00 2018_02-11

Since the 3rd row 'XX 001 123 22.00 2018-03-12' has the same value for Field_1 and Field_2 as the 1st one. Either there is a mistake in your example and result, or I didn't understand your task.
Best regards,
Oleg Zhechkov
User avatar
Admin
Site Admin
 
Posts: 124
Joined: Wed Apr 21, 2010 7:27 pm


Return to DBF Commander Professional

Who is online

Users browsing this forum: No registered users and 7 guests

cron