Splitting a page on a SQL Server connection
This is my request:
select * from (
select u.id, 'u' as [type], u.firstName, u.lastName, c.name as companyName,
u.lastName + u.firstName + isNull(c.name, '') as sortName
from users as u
left outer join companies as c on c.id = u.company_id
union all
select id, 'c' as [type], null as firstName, null as lastName, name as
companyName, name as sortName
from companies
) as result
where sortName like '%a%'
order by sortName
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
It returns 10 rows from tables users
and companies
that match the search string "a". With offset / fetch, I can easily do pagination in my application. But I still need to know how many rows are in the database that matches the search string "a" without a fetch limit.
My first approach was count(*)
to query for the result, but that doesn't work (due to concatenation, I think).
+3
source to share
2 answers
You can try adding COUNT (*) OVER () to your selection * as shown below. The total number of rows will be in a new column named "total_rows" in the example below.
select *, total_rows=COUNT(*) OVER() from (
select u.id, 'u' as [type], u.firstName, u.lastName, c.name as companyName,
u.lastName + u.firstName + isNull(c.name, '') as sortName
from users as u
left outer join companies as c on c.id = u.company_id
union all
select id, 'c' as [type], null as firstName, null as lastName, name as
companyName, name as sortName
from companies
) as result
where sortName like '%a%'
order by sortName
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
0
source to share