SQL rowcount and total count for pagination in duplicate value

SQL duplicate value and rowcount:-
   Some situation row_counts and Total_Counts need depends on the some duplicate values and so this situation we can use the Dense_RANK() and RANK() methods.

When I selecting a table my output is come likes below
  Select * from office_list_of_clearance;

EmpID	EmpCode	Empname	Date	ID	ClrID	ClrName	Status
100	EMP01	Raj	31 Jul 2014	2	60	clearance	0
100	EMP01	Raj	31 Jul 2014	3	61	Test2	0
101	EMP02	Jino	10 Aug 2014	4	63	Test3	0
102	EMP03	Reegan	30 Jul 2014	5	68	Test4	1
102	EMP03	Reegan	30 Jul 2014	6	113	Test5	1
102	EMP03	Reegan	30 Jul 2014	7	114	Test6	0
103	EMP04	Rajesh	27 May 2014	8	115	Test7	0

  
In the above table first two row's are indicate the first employee details,3rd Row indicate the second employee details,4,5,6the rows indicate the Third employee details and 7th row indicate the 4th employee details
    So I need the total count is : 4 and Row count : 1,1,2,3,3,3,4 and Row Count1:1,2,3,4,5,6,7

Query:-

with tbl as(
select convert(int,Dense_RANK() over (order by EmployeeID)) as RowNum,* from(  
select convert(int,row_number() over(order by EmployeeID)) as RowNum1,* from(
select --convert(int,COUNT(*) over()) as count,
* from office_list_of_clearance 
)x)xx)

The output will come like

TC	RowN	RowN1	EID	ECode	Ename	Date	ID	ClrID	ClrName	Status
4	1	1	100	EMP01	Raj	31 Jul 2014	2	60	clear1	0
4	1	2	100	EMP01	Raj	31 Jul 2014	3	61	Test2	0
4	2	3	101	EMP02	Jino	10 Aug 2014	4	63	Test3	0
4	3	4	102	EMP03	Reeg	30 Jul 2014	5	68	Test4	1
4	3	5	102	EMP03	Reeg	30 Jul 2014	6	113	Test5	1
4	3	6	102	EMP03	Reeg	30 Jul 2014	7	114	Test6	0
4	4	7	103	EMP04	Raja	27 May 2014	8	115	Test7	0

Advertisement

Screen shot:-