Sql Get Row_number And Count On Every Select Request

I´m building a grid mechanism where I need to retrieve data from Database the total or records found, retrieving just a range of these records with a row_number in it. I´m using

Solution 1:

This is probably meant to allow paginating. Showing the total number of rows can be really expensive.

One way that I've found to work well on a variety of databases is to split the work in two parts. First, you collect the ID of the relevant rows in a temporary table. Second, you query the full dataset. The data collected in the first part gives you an easy way to calculate the total number of rows and the IDs of rows on a certain page.

Here's a rough example for SQL Server. Note that the example does not rely on window functions like row_number(), which are not available in MySQL.

createtable #id_list (rn intidentity, pk int);

insert   #id_list
         (pk)
select   customer_id
from     customers
where    name like'%Joe%';

select   (selectcount(*) from #id_list) as total_rows
,        rn -- The row's number
,        name
,        birth_date
,        ... -- Other columnsfrom     #id_list id
join     customer c
on       c.pk = c.customer_id
where    rn between15and29; -- Second 15-row page 

By the way, if feasible, I'd return this requirement to the designers to double check if this is worth spending a lot of time on. It is much simpler if you do not need to display the total number of rows.

Solution 2:

To include a COUNT(*) in the same SELECT clause with a bunch of detail rows is to fail. That's because COUNT(*) is an aggregate function. In the absence of GROUP BY it returns only one row in its result set. So, you need to query the count in its own SELECT statement.

SQL Server has the ROW_NUMBER() function. Oracle has the pseudocolumn named ROWNUM. And MySQL has a truly miserable hack for getting row numbers. It's described here. MySQL - Get row number on select