CODEWITHSUNDEEP
sqloracle-databasepagination
Michael Edwards
Michael Edwards

Reputation: 49

Select both row number and count from oracle

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

Upvotes: 0

Views: 223

Answers (1)

abdallahsaboukh
abdallahsaboukh

Reputation: 11

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

Upvotes: 1

Related Questions