Skip to content Skip to sidebar Skip to footer

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:-
SQL duplicate value and rowcount

Post a Comment for "SQL rowcount and total count for pagination in duplicate value"