Having problem with distinct and count
Hi,
Here is the query I am trying to achieve and having syntax issues
Select count(distinct name, number) from results.
To replicate the situation use the following SQL
create table results (name varchar(100), number int)
insert into results values ('test1', 1)
insert into results values ('test1', 1)
insert into results values ('test1', 1)
insert into results values ('test2', 2)
insert into results values ('test2', 2)
insert into results values ('test2', 2)
Basically the return of the query should be 2. I can achieve this by
doing following query
select count(*) from
(select distinct [name], [number] from results) a
but I want to do it one query as the later query is a big hit on the
performance.
On a large sample of data the second query takes around 2 seconds.
In message <1136478618.777363.213780@f14g2000cwb.googlegroups.com>, Sai
<sbillanuka@gmail.com> writes
>Hi,
>
>Here is the query I am trying to achieve and having syntax issues
>
>Select count(distinct name, number) from results.>
>To replicate the situation use the following SQL
>
>create table results (name varchar(100), number int)
>insert into results values ('test1', 1)
>insert into results values ('test1', 1)
>insert into results values ('test1', 1)
>insert into results values ('test2', 2)
>insert into results values ('test2', 2)
>insert into results values ('test2', 2)>
>Basically the return of the query should be 2. I can achieve this by
>doing following query
>select count(*) from
>(select distinct [name], [number] from results) a>
>but I want to do it one query as the later query is a big hit on the
>performance.
>
>On a large sample of data the second query takes around 2 seconds.
>
If you had a syntax problem the query would not run. You have a
performance issue, and quite possibly a data analysis issue.
What version of Informix? What operating system? What is the schema of
the table, including indexes? What does 'a large sample of data' mean?
Have you done UPDATE STATISTICS for the table after loading the data?
--
Surfer!
Email to: ramwater at uk2 dot net