Re: 4gl Question
>
> I have an interesting 4gl question. I have a table which contains column a
> and column b. I'm trying to get a count on unique column b using column a
> as the search criteria.
>
... ( stuff deleted ) ...
> I'm interested in seeing a better way and I'd rather not use a view. We
> are looking for a glamorous SQL statement.
>
Ted,
This should do it:
select count(unique b) into the_count
from table where a = "XX";
The following is the test sql script I used to try it out:
{ create a table and insert data }
create table lk ( a char(2), b smallint);
insert into lk values ( "XX", 22 );
insert into lk values ( "XX", 11 );
insert into lk values ( "YY", 22 );
insert into lk values ( "YY", 33 );
insert into lk values ( "XX", 33 );
insert into lk values ( "XX", 44 );
{ insert duplicate data }
insert into lk values ( "XX", 44 );
{ select all rows where a = "XX - returns 5 rows }
select count(*) from lk where a = "XX";
{ select unique rows where a = "XX - returns 4 rows }
select count(unique b) from lk where a = "XX";
Regards - Lester
#############################################################################
# Lester Knutsen lester@access.digex.net #
# Advanced DataTools Corporation Voice: 703-256-0267 #
# Providing Informix Database Tools and Consulting #
#############################################################################