One of our developers came up with the following query:

select a.filterid, a.filtername, a.scope, a.tableid, count(b.filtername) as
Rank
from filters as a, filters as b
where a.filtername>=b.filtername
group by a.filterid, a.filtername, a.scope, a.tableid
having (count(b.filtername) = xxx)



The 'xxx' is where you'd put a number. The way it works is this: If I have
20 rows in my filters table, xxx could be any value between 1 and 20 and it
will, essentially, enumerate all the rows. This works beautifully.

If I make the following change:

select a.filterid, a.filtername, a.scope, a.tableid, count(b.filtername) as
Rank
from filters as a, filters as b
where a.filtername>=b.filtername and a.tableid = yyy
group by a.filterid, a.filtername, a.scope, a.tableid
having (count(b.filtername) = xxx)

adding the a.tableid = yyy where 'yyy' is a valid value for tableid, I get
an empty recordset back for rows that don't have a tableid of yyy. For the
rows that do have a tableid of yyy, I get back a row.

This was not my expected result. The result I was looking for was to
enumerate through the tables that matched yyy. Instead, it's as if I were
doing the first query, but where tableid is not equal to yyy, I get an empty
recordset back.

The query should alway return 1 row and 1 row only. As I said, it enumerates
through the rows... Any idea on how to make the modification I want?

Hope I've explained this properly.

Pete Davis