I am developing a component that makes a list with pagination, in order to do that I have an sql generator.

I need to get the number of rows that the sql query will produce, but I need to count by several columns:

SELECT COUNT(A.id, B.id, ...)
FROM table1 as A, table2 as B
WHERE .....

I am using Informix 10, then this behavior is not supported, I think that I can use a subquery:

SELECT COUNT(*)
FROM (SELECT COUNT(A.id, B.id, ...)
FROM table1 as A, table2 as B
WHERE .....) as D

But is unsupported too in Informix 10 (in Mysql the two approaches are supported)

Some ideas?

With a regular join the count function returns the number of rows that satisfy the conditions. But this is very straightforward, can't imagine MySql needs an alternate construction for that, do you need a count of rows involved in an outer join perhaps? Then you need to use something like:Regards

Tyveleyn, some times a DISTINCT clause can filter the results (the sql generator generates DISCTINT if the user ask for it), this is the reason I need to count by several columns, I have found a solution using multiset:

select count(*) as rownum
from table(multiset(SELECT DISTINCT.....)) s1;

Then I can get the number of rows of any sql query using a multiset, or I am wrong, there should be any restriction in make a subquery for a multiset?

As I wrote, I have found a solution for my problem, this is using TABLE and MULTISET:

SELECT COUNT(*)
FROM TABLE(MULTISET(SELECT DISTINCT a,b,c .....))

But this solution is very slow, the subquery is not a very optimal approach, some ideas to improve the solution?

Regards

I don't know if the MULTISET datatype is appropriate in your case. It's a datatype for a collection column, that stores multiple values in one attribute and makes the table a kind of multidimensional.
What else you could do is perform your task with subqueries, like:In this case the count of the rows in the subqueries are independent from the number of results in the main query.

Regards

I think that I have not explained successfully the problem, I need to get the rows of a generated query, e.g. the query may be:

select DISTINCT companyid, employeeid from report

Then I need to get the rows generated by this query, a query like:

select COUNT(DISTINCT companyid, employeeid) from report

But this don´t works in Informix 10, in mysql this executes without problems.

I have found a solution with MULTISET:

select COUNT(*)
from TABLE(MULTISET(select DISTINCT companyid, employeeid from report))

But this query is really slow. Some ideas?