We found that SELECT DISTINCT sentence returns wrong values.
Executing with Squirrel the following sentence:
We get the following values as a result:
Biomet Deutschland 2011
Biomet France 1209
Biomet UK 1924
Changzhou Biomet Medical Devices Co., Ltd 346
Faulhaber Pinzetten OGH 112
Kapp Surgical GmbH 100
Oertel Medizintechnick 49
Zimmer GmbH 1257
Using MS Excel filter on the column "Location5" we get a different value set.
For example we found the following similar values:
Biomet Deutschland GmbH
Running SELECT COUNT(*) for those values we found respectively 1407 and 604 records.
Please let us know a mail address to send you the Xls file.
PS: We found similar problems with XLSX files
You can send it to email@example.com .
But in SQL, Biomet Deutschland != Biomet Deutschland GmbH
You can use
select "company name",count(*),LEFT("company name",RATC(' ',"company name")) as key from test.atable group by key; /* Biomet Deutschland = Biomet Deutschland GmbH*/
select "company name",count(*),LEFT("company name",RATC(' ',"company name",2)) as key from test.atable group by key; /* Biomet = Biomet Deutschland = Biomet Deutschland GmbH*/
Recurred your issue:
select location5,count(*) from listmex.Hoja1 group by location5
You can add maxScanRows=0 or maxScanRows=-1 connection property, then your issue will disappear.
maxScanRows: Indicates how many rows should be scanned when determining the column types. If you set maxScanRows to 0, the entire file is scanned. If you set maxScanRows to a negative value, the file won't be scanned. Default value: 10