Friday, March 30, 2012

Having problems 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.
Any help would be appreciated.
Thanks
SAIan index on (name, number) might speed it up

No comments:

Post a Comment