| Subject | Re: [firebird-support] COUNT DISTINCT issue in Fb 2.5 |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2017-09-05T07:29:39Z |
> select a.num_part, a.cve_prov, a.cve_impo, a.des_merc> from ctrac_clasif a> where a.num_part = '0630039046';>>It yields this results:>>NUM_PART CVE_PROV CVE_IMPO DES_MERC>========= ========= ========= ==========>0630039046 3400. 19 CUBIERTA>0630039046 3400. 19 CUBIERTA>>As you can see, the two resulting rows are identical.
For the human eye, yes, for a computer, maybe.
>Now I run the following aggregate query:
>select a.num_part,a.cve_prov,a.cve_impo,COUNT(DISTINCT a.des_merc)
>from ctrac_clasif a
>where a.num_part = '0630039046'
>group by a.num_part,a.cve_prov,a.cve_impo
>
>NUM_PART CVE_PROV CVE_IMPO COUNT
>========= ========= ========= ======
>0630039046 3400. 19 2
So they are different for your computer.
Is DES_MERC defined as BLOB or something similar? If so, change to something like:
select a.num_part,a.cve_prov,a.cve_impo,COUNT(DISTINCT cast(a.des_merc as varchar(32)))
from ctrac_clasif a
where a.num_part = '0630039046'
group by a.num_part,a.cve_prov,a.cve_impo
If not, do as Karol asks and show us how to replicate your case. At least, I only get one row if I do
SELECT 'CUBIERTA' FROM RDB$DATABASE
UNION
SELECT 'CUBIERTA ' FROM RDB$DATABASE
whereas two rows are returned if I do
SELECT CAST('CUBIERTA' AS BLOB CHARACTER SET ISO8859_1) FROM RDB$DATABASE
UNION
SELECT CAST('CUBIERTA' AS BLOB CHARACTER SET ISO8859_1) FROM RDB$DATABASE
HTH,
Set