Untitled
unknown
plain_text
a year ago
1.9 kB
4
Indexable
28/02/2024 =========================================================================================== window function ranking:- use testdb; select * from customer; select cust_id ,row_number() over(order by cust_id) ,rank() over(order by cust_id) ,dense_rank() over(order by cust_id) from customer; select cust_id ,address ,row_number() over(partition by address order by cust_id) , rank() over(partition by address order by cust_id) ,dense_rank() over(partition by address order by cust_id) from customer; select cust_id ,address ,age,row_number() over(partition by address order by age) , rank() over(partition by address order by age) ,dense_rank() over(partition by address order by age) from customer; update customer set age=34 where cust_id=101; insert into customer value(112,"ram","pune",45) =============================================================================================================================== Analytic function:- select * from customer; select cust_id,cust_name,lead(cust_id) over(order by cust_id),lag(cust_id) over(order by cust_id) from customer; select cust_id,address,lead(cust_id) over(partition by address order by cust_id),lag(cust_id) over(partition by address order by cust_id) from customer; select cust_id,first_value(cust_id) over(),last_value(cust_id) over() from customer; select cust_id,first_value(cust_id) over(order by cust_id),last_value(cust_id) over(order by cust_id) from customer; select cust_id,address,first_value(cust_id) over(partition by address order by cust_id),last_value(cust_id) over(partition by address order by cust_id) from customer. select cust_id,cust_name,lead(cust_id) over(order by cust_id),lag(cust_id) over(order by cust_id) from customer; select cust_id,cust_name,lead(cust_id,4) over(order by cust_id),lag(cust_id,4) over(order by cust_id) from customer;
Editor is loading...
Leave a Comment