0

I'm trying to select records with pagination, and I need the total number of records so that I can display the number of records and pages on the UI.

The query I'm using is as below but it always returning the totalcount as 1.

WITH cteEmp AS (SELECT e.empid, e.empname, d.deptid, d.deptname FROM hr.Emp e INNER JOIN hr.dept d ON e.deptid = d.deptid) Select * from (SELECT row_number() over (order by hr.empid desc) rn, Count(*) totalcount, C.empName FROM CTEPO C LEFT JOIN hr.emphistory ON C.empid=hr.empid GROUP BY c.empid,hr.empid) where rn>0 and rn<= 100
Improve this question
1
  • Please edit your question to include a minimal reproducible example with: the CREATE TABLE statements for a minimal example of your table; the INSERT statements for some sample data; and your expected output for that sample data. Commented Oct 3, 2021 at 13:07

1 Answer 1

Reset to default
1

You can try this maybe it'll work for you:

(SELECT e.empid, e.empname, d.deptid, d.deptname FROM hr.Emp e INNER JOIN hr.dept d ON e.deptid = d.deptid) Select * from (SELECT row_number() over (order by hr.empid desc) rn, count(*) OVER (ORDER BY hr.empid desc ) AS totalcount C.empName FROM CTEPO C LEFT JOIN hr.emphistory ON C.empid=hr.empid GROUP BY c.empid,hr.empid) where rn>0 and rn<= 100
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.