Untitled

14/02/2024
-------------------
window function 

1 with agrregate function :
use first;
select * from employeeinfo;
select count(*) from employeeinfo group by salary;
select id, name ,salary,count(id) over()  as total from employeeinfo;
select id, name ,salary,count(id) over(order by salary)  as total from employeeinfo;
select id, name ,salary,count(id) over(partition by salary order by salary)  as total from employeeinfo;

2.ranking:

-: row_number: row_number function can provide a unique sequnce number for each row.
starting with 1, according to the ordering of rows within the window partition.

-: rank:-rank() function is used to genearate rank for each row within a result set.
rank function gives you to ranking within your ordered partition.
tier are assign same rank, with the next ranking skipped,so if you have 3 items at rank 4, the next rank listed would be ranked 7.

:-dence_rank: dence_rank gives  you the ranking within your orderd partition, but the rank are consecutive . no rank are skipped if there ranks with multiple items.

select * , row_number() over(order by salary ) as rownumber,rank() over(order by salary ) as rankin,dense_rank() over(order by salary ) as drankin from employeeinfo;
select * , row_number() over(partition by address order by salary ) as rownumber ,
rank() over(partition by address order by salary ) as rnk,
dense_rank() over(partition by address order by salary ) as dence_rnk from employeeinfo;