SQL get ROW_NUMBER and COUNT on every SELECT request

Question

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 SqlServer for testing, but I need to support that on Oracle and MySql as well.

That´s what I´m trying, but I can´t make it work:

    SELECT * FROM 
           (SELECT ROW_NUMBER() AS RN, 
                   COUNT(*) AS TOTALCN, 
                   Id, 
                   Name, 
                   Phone 
            FROM MyTable WHERE Deleted='F')
            WHERE RN > 100 AND RN < 150;

The idea is:

MyTable -> number of records: 1000
Select Id, Name, Phone from MyTable where Deleted='F' -> number of records: 850
Get the records 100 to 150 from the 850.

I want to get a select like:

RN   TOTALCN Id    Name   Phone
1    850     Data  Data   Data
2    850     Data  Data   Data
3    850     Data  Data   Data
4    850     Data  Data   Data
5    850     Data  Data   Data
6    850     Data  Data   Data

The CN (850) will be my grid total number of records. The RN will be grid index for data.

CAn someone help me to accomplish that ?

Thanks for any help.

[EDIT]

So, I will defenetely add an ORDER BY on every SELECT. Here is what I´m getting so far:

SELECT * FROM (SELECT ROW_NUMBER() 
              OVER (ORDER BY ID)  AS RN, 
              COUNT(*) OVER (ORDER BY (SELECT NULL) AS CNT) 
              Id, Name Phone FROM MyTable WHERE Deleted='F') 
T WHERE RN > 100 AND RN < 500;

Am I going to the right way ?

How expensive will be that to SQL ?

well is this MySQL or SQL?? the languages aren't the same. – John Ruddell Jul 24 '14 at 20:42 — John Ruddell, Jul 24 '14 at 20:42

Andomar · Answer 1 · 2014-07-24T18:18:39.153

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.

create table #id_list (rn int identity, pk int);

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

select   (select  count(*) from #id_list) as total_rows
,        rn -- The row's number
,        name
,        birth_date
,        ... -- Other columns
from     #id_list id
join     customer c
on       c.pk = c.customer_id
where    rn between 15 and 29; -- 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.

I think this will be very expensive compared to adding a order by... Am I right ? — Mendes, Jul 24 '14 at 18:38
In my tests this is the best performing solution. If I were you I'd write a few alternatives and test them! — Andomar, Jul 25 '14 at 04:53

score 1 · Accepted Answer · edited May 23 '17 at 11:44

1

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

edited May 23 '17 at 11:44

Community

1
1

answered Jul 24 '14 at 18:11

O. Jones

103,626
17
118
172

SO, I can understand os my options are: (a) Add a order by on every select, (b) Do 2 different queries - one for count and one for data ? – Mendes Jul 24 '14 at 18:35
1

Oracle has `row_number()` too; and an analytic version of `count()` which doesn't need the `group by` clause and doesn't need its own `select`. Trying to find one solution that fits all three databases is probably going to cause heartache though... – Alex Poole Jul 24 '14 at 19:19

SQL get ROW_NUMBER and COUNT on every SELECT request

2 Answers2

Linked