SQL Window Function with Example- RANK, DENSE RANK, ROW NUMBER, LEAD/LAG

No video

SQL Window Function with Example- RANK, DENSE RANK, ROW NUMBER, LEAD/LAG | SQL Tutorial in Hindi 14

Рет қаралды 172,760

Rishabh Mishra

Күн бұрын

Пікірлер: 292

@ambujtiwari4275 8 ай бұрын

Bhai sach bta rha hu....ekdm izzat se padha aur smjha de rhe ho ❤

@RishabhMishraOfficial 8 ай бұрын

Glad you liked it ✅️

@official_mahendra221 11 ай бұрын

Rishab bhai, maine 80% sql apke videos se sikha hai, thanks .........thanks lot ..........lots of love !

@RishabhMishraOfficial 11 ай бұрын

Glad it was helpful ✅️

@Erengaming535 13 күн бұрын

Sir window function jis tarah se aapne samjhaya hai merko mere to dimag mein chap gaya thank you sir ye sql serious follow karke bhot kuch sikhne ko mila thanks 🙏🙏🙏❤️❤️

@RishabhMishraOfficial 12 күн бұрын

Glad you liked ✅️

@tejasthete7007 Жыл бұрын

It is really help full for me and i found roll model for data analyst thanks bro for enhance our skill, please Rishabh make a vedio on real time experience of data analyst project so that we can get clarity please.

@RishabhMishraOfficial Жыл бұрын

Glad you liked it, thanks a lot. Yes i will make a complete video on how we analyze data using Python from scratch

@tejasthete7007 Жыл бұрын

Thanks we will wait your movement

@labdhibavishi2430 4 ай бұрын

Did you get a job?@@tejasthete7007

@infotech5370 4 ай бұрын

When we are using order by new_id , following is the output I am getting in contrast with the output shown in the video new_id new_cat Total 100 Agni 100 200 Agni 300 500 Dharti 500 700 Dharti 1200 200 Vayu 200 300 Vayu 500 500 Vayu 1000

@mdsarwaralam3485 15 күн бұрын

Removing the ORDER BY clause from the window functions in your query, give same output as in pdf Kindly, see below query- SELECT new_id, new_cat, SUM(new_id) OVER (PARTITION BY new_cat) AS "Total", AVG(new_id) OVER (PARTITION BY new_cat) AS "Average", COUNT(new_id) OVER (PARTITION BY new_cat) AS "Count", MIN(new_id) OVER (PARTITION BY new_cat) AS "Min", MAX(new_id) OVER (PARTITION BY new_cat) AS "Max" FROM test_data; or use- "ORDER BY new_cat" instead of "ORDER BY new_id" SELECT new_id, new_cat, SUM(new_id) OVER (PARTITION BY new_cat ORDER BY new_cat) AS "Total", AVG(new_id) OVER (PARTITION BY new_cat ORDER BY new_cat) AS "Average", COUNT(new_id) OVER (PARTITION BY new_cat ORDER BY new_cat) AS "Count", MIN(new_id) OVER (PARTITION BY new_cat ORDER BY new_cat) AS "Min", MAX(new_id) OVER (PARTITION BY new_cat ORDER BY new_cat) AS "Max" FROM test_data

@randomvideo7891 5 күн бұрын

Thnx @@mdsarwaralam3485

@pkabc3454 5 ай бұрын

in aggregate fiction ex:- after order by add new_cat not include new_id ,Because new_id result are 100,300 and new_cat result are 300,300

@vishalkumar-hi7wi 2 ай бұрын

thx bro

@GovindBisht-le4xx Ай бұрын

same problem bro . if you get any solution please share with me

@sadiqahassan 3 ай бұрын

Really liked your teaching style Mr. Rishabh Mishra Sir

@aqawewewe 4 ай бұрын

Really liked your teaching style bro .. keep it up

@Ap.underscore Жыл бұрын

To summarize, "Rank" leaves gaps if there are ties (same scores), while "Dense Rank" doesn't leave any gaps and keeps counting without skipping any numbers when there are ties.

@RishabhMishraOfficial Жыл бұрын

Awesome 👌

@poojamahajan1878 17 күн бұрын

Really like your Teaching Rishab keep it up.

@RishabhMishraOfficial 16 күн бұрын

Glad you liked ✅️

@saeelsalvi1739 3 күн бұрын

00:01 Windows functions apply aggregate, ranking, or analytic functions on a window 01:42 SQL window functions allow for aggregate operations over a set of rows. 05:03 SQL window functions summarize data for analysis. 06:41 SQL Window Functions can be used to partition and order data in SQL queries. 10:14 SQL window functions explained with examples 11:58 Understanding SQL Window Functions - RANK and DENSE RANK 15:13 Understanding SQL Window Functions - RANK, DENSE RANK, LEAD/LAG 16:50 Understanding the functionality of lead function in SQL Window Function 19:48 Understanding the functionalities of RANK, DENSE RANK, ROW NUMBER, LEAD, and LAG in SQL Window Functions 21:14 Understanding lead, lag, and window functions for interview preparation

@Userlearner2229 8 ай бұрын

"IZZAT SE CHALTA HAI" 😄😄😄 THANK YOU Rishabh Bhaiya , lot of thanks

@Adityasahu0039 27 күн бұрын

clean and simple, I like the way you explained everything.

@RishabhMishraOfficial 25 күн бұрын

Glad you liked it! ✅️

@Bobby-mf6fw 28 күн бұрын

Best sql videos on youtube.

@RishabhMishraOfficial 28 күн бұрын

Glad you liked it ✅️

@MubarakAli-qs9qq 2 күн бұрын

Sir u explained very nicely

@RishabhMishraOfficial Күн бұрын

Glad you liked ✅️

@manavjain9648 9 ай бұрын

great video especially for interviews

@RishabhMishraOfficial 9 ай бұрын

Glad you like them! 👍

@ReviewwithAshish-qc8xb 22 күн бұрын

Rishabh bhai thank you so much. I am preparing for data analyst interview and your video's are helping me a lot to understand the SQL more easily

@baki_1205 2 ай бұрын

Superb @Rishabh Mishra sir❤

@arhaam_val Ай бұрын

Thanks... Really helpful!

@RishabhMishraOfficial Ай бұрын

Glad it helped! ✅️

@Divyanshu_Arts 3 күн бұрын

Thanku soo much sir ❤

@RishabhMishraOfficial 3 күн бұрын

Glad you liked ✅️

@automationwithwasi Жыл бұрын

Thanks for the videos, very useful!! I got the answer to the question you asked without using the method. First, we will get the columns with lead and lag then apply the lead and lag again on those columns.🙃

@RishabhMishraOfficial Жыл бұрын

Awesome, keep learning n keep growing 🚀

@Vaishnavikaithwas786 2 ай бұрын

Thanks for these superb explaination sir..... I will never forget these topic bcoz of ur wonderful teaching

@RishabhMishraOfficial 2 ай бұрын

Glad you liked ✅️

@Pritamgogreen 10 ай бұрын

Thank you so much for the clear and fluid explanation. My search for a good explanation of "window function" ends here.

@RishabhMishraOfficial 10 ай бұрын

Glad it helped! ✅️

@annusharma9622 4 күн бұрын

Thanks a lot sir ☺️

@RishabhMishraOfficial 3 күн бұрын

Glad you liked ✅️

@sanketsarolkar9444 6 ай бұрын

Bhai tumhare videos sabse best aur easy way mein samjh mein ate hai….❤

@RishabhMishraOfficial 6 ай бұрын

Glad you liked ✅️

@sudiptanath4103 Жыл бұрын

Thank you rishabh...

@yasink18 Жыл бұрын

Sir you taught in a very simple manner.. Love your tutorial

@RishabhMishraOfficial Жыл бұрын

Glad you liked ✅️

@shubhverma4579 Жыл бұрын

sir! jab kuchh search krta hu sql ke liye hindi mein or apki shakal dikh jati hai tho search krna chhod deta hu kynki dil se ek hi awaz aati hai,"Bhai! hai tho apna." #Thanks_for_teach_us_sir

@princekhunt13579 11 күн бұрын

good content

@RishabhMishraOfficial 7 күн бұрын

Glad you liked ✅️

@himanshushukla9229 10 ай бұрын

bht hi awesome bhai.. perfectly explained thanks

@RishabhMishraOfficial 9 ай бұрын

Glad it was helpful ✅️

@nehamegha3384 Жыл бұрын

Thanku so much sir for the tutorial 🤩🤩🤩🤩 crystal clear explanation 😍😍😍😍 searching everywhere but finally got the best tutorial thanks alot

@RishabhMishraOfficial Жыл бұрын

Glad it was helpful ✅️

@sandhyapatro8688 Жыл бұрын

Very well explained Rishabh.

@RishabhMishraOfficial Жыл бұрын

Glad you liked ✅️

@MuhammadSaleem-id2ye 2 ай бұрын

Thanks and Love from Pakistan

@bhaveshbharti4782 Жыл бұрын

thankyou so much kitna acha padhaya hai

@RishabhMishraOfficial Жыл бұрын

Glad you liked it ✅️

@george8687 Жыл бұрын

Really nice video mate! You got a new subscriber.

@RishabhMishraOfficial Жыл бұрын

Glad you liked it n Welcome aboard! 🚀

@aniketbanerjee3463 Жыл бұрын

very easy to understand, nice video.

@RishabhMishraOfficial Жыл бұрын

Glad it was helpful! ✅️

@MamataHati-e3m Ай бұрын

Thanks for this vdo

@RishabhMishraOfficial Ай бұрын

Glad you liked ✅️

@sonalsingh4151 Жыл бұрын

Thanks for explaining windows fun..really helpfull

@RishabhMishraOfficial Жыл бұрын

Glad you liked it 👍

@jjk435 11 ай бұрын

Been looking for it. In a world full of free resources, found this of great help ❤.thank you

@RishabhMishraOfficial 11 ай бұрын

Glad I could help!

@yashaggarwal7701 Жыл бұрын

thanks bro for this wonderful explaination of window function

@RishabhMishraOfficial Жыл бұрын

Glad you liked it ✅️

@ronakpatel8185 7 ай бұрын

In 6:57 the output is different from the expected output, so just remove the order by clause and u are good to go

@swarnimamishra7007 6 ай бұрын

or use ORDER BY new_cat instead of new_id

@eddybear9436 5 ай бұрын

@@swarnimamishra7007exactly. Ab sahi aa tha hai. Otherwise to Sum aur average me first value of I'd as it is print ho rhi thi

@shristikumari8828 Жыл бұрын

Simplest explaination ever.🌹

@RishabhMishraOfficial Жыл бұрын

Glad you liked it

@introvertwhiz-ll2ip 26 күн бұрын

Explanation superb, make him the professor of computer science Department.

@RishabhMishraOfficial 25 күн бұрын

Glad you liked the content ✅️

@andythomson2227 5 ай бұрын

Excellent teaching skills❤

@RishabhMishraOfficial 5 ай бұрын

Glad you liked ✅️

@anshuljain3105 28 күн бұрын

i have and interview and it helped me a lot

@RishabhMishraOfficial 28 күн бұрын

Awesome, keep learning n keep growing 🚀

@ArmaanKing17 3 ай бұрын

Very well explained ❤

@RishabhMishraOfficial 3 ай бұрын

Glad you liked ✅️

@vineetanand5341 Жыл бұрын

Good Video. Please share share more videos with real Time example. Also 1 query, For functions like FIRST_VALUE, KST_VALUE, LEAD & LAG in select clause we can have more col's & function can be applied on any one col or more col also?

@varshamate7566 Жыл бұрын

Thanku so much,I understand very well

@sharma-vasundhara 10 ай бұрын

Made it very simple!

@RishabhMishraOfficial 10 ай бұрын

Glad you liked ✅️

@Product25012 10 ай бұрын

At first a great thanks for ur explanation, Ekk Doubt h Hame kb smjh ayega ki window function use krna h ya normal sql query lgana h ......Plzzz Reply

@RishabhMishraOfficial 10 ай бұрын

Practice n you will understand

@ullua5671 Ай бұрын

Best video on SQL

@DPMotivationDhruvPandit Ай бұрын

Bahut badiya tutorial banaya hai rishav bhai aapne ❤

@adwaydandwate 3 ай бұрын

Hi Rishabh, thanks for your video. I see there is one discrepancy in output of first example: "Partition BY new_cat Order BY "new_id". This doesn't give the output as shown in the picture in the video. It has to be ordered by "new_cat" instead of "new_id", then you will get that output. Eg: Partition BY new_cat Order BY new_cat. Please try this once and let me know if am wrong here. Thanks!

@abhishekkumarjha2352 3 ай бұрын

good observation, with the query mentioned in the video we will get the cumulative sum in that group

@AnandPatil-eu1tl 4 ай бұрын

Thank You Rishabh

@RishabhMishraOfficial 4 ай бұрын

Glad you liked ✅️

@hoodal__ob851 Жыл бұрын

Great bhaiya❤❤

@gauravpal15693 Жыл бұрын

Thanks for the videos, very useful!!

@RishabhMishraOfficial Жыл бұрын

Glad you like them! 👍

@user-jb3cx2qk8b 19 күн бұрын

Pyspark playlist please..

@dheerajkoranga5012 2 ай бұрын

smooth like a smoothie :) before : window function is difficult to understand after this video: what! window function is sooo easy

@RishabhMishraOfficial 2 ай бұрын

Glad you liked ✅️

@saikatbagh8845 3 ай бұрын

Window functions maintain the individual rows while also providing aggregate-like results.

@ravindraslahane3507 Ай бұрын

Very well explained

@pavan.avasthi Ай бұрын

Thanks sir ❤

@rubysrivastava2288 11 ай бұрын

The output for the first query will be: new_id new_cat total 100 Agni 100 200 Agni 300 500 Dharti 500 700 Dharti 1200 200 Vayu 200 300 Vayu 500 500 Vayu 1000 Here, ORDER BY clause serves the dual purpose of sorting the data and calculating the cumulative sum or any other aggregate calculations within the window. @Rishabh please clarify here.

@RishabhMishraOfficial 11 ай бұрын

Here we have sorted the data based on category n total column. Eg- Agni house is sorted first as it starts with alphabet 'A' n then in agni house first value is 100 followed by 300 which is again sorted

@aseemanand1 10 ай бұрын

@@RishabhMishraOfficialman did you understand what she asked? She’s asking about the total column’s output. Shouldn’t it be the cumulative sum/running sum?

@ape789 10 ай бұрын

Actually the order by should be below the from statement and outside the over clause. inside the over bracket it is working on cumulative way.

@poojadange9088 9 ай бұрын

@@ape789 yes if we did it in that way as u said it is working as normal sum not like cumulative

@shraddhadhakad1154 8 ай бұрын

SELECT new_id, FIRST_VALUE(new_id) over(order by new_id) as "First_Value", LAST_VALUE(new_id) over(order by new_id) as "Last_Value", LEAD(new_id) over(order by new_id) as "Lead", LAG(new_id) over(order by new_id) as "LAG" FROM [practice_sd].[dbo].[windows function]

@akankshasoni3479 Жыл бұрын

Thank you so much bhaiya for uploading such a wonderful content onto KZfaq which is of great use to beginners like me

@RishabhMishraOfficial Жыл бұрын

Glad you liked it ✅️

@poojamankotia7353 Жыл бұрын

Hello, first of all your videos are really helpful, thankyou. Secondly, I am trying to run the Windows Aggregate func but its giving different input, kinldy help. Attaching the code and the output below: select new_id, new_cat, sum(new_id) over(partition by new_cat order by new_id)as "Total" new_id new_cat Total 100 Agni 100 200 Agni 300 700 Dharti 700 500 Dharti 500 200 Vayu 200 300 Vayu 500 500 Vayu 1000 from test_data

@vansaber 10 ай бұрын

If you remove the ORDER BY from the statement or ORDER BY new_cat then it will display exactly like in the video.

@AnjaliKumari-fz9lr 6 ай бұрын

Thanks for such a easy to understand video. ❤❤ Also I have se concern regarding data analyst job like ....is it necessary to know ML also or predictive modelling for this job??

@RishabhMishraOfficial 6 ай бұрын

It's good to hv but not compulsory

@111rhishishranjan2 11 ай бұрын

Hello Bhaiya, Actually You forgot to make interview question videos related to window functions and LEAD/LAG and its been quite a while like 10 months , please do it, it would be much helpful for us because your explination is really great .

@RishabhMishraOfficial 11 ай бұрын

Noted ✅️

@shraddhadhakad1154 8 ай бұрын

SELECT new_id,new_cat, sum(new_id) over(order by new_id rows between unbounded preceding and unbounded following) as "Total", AVG(new_id) over(order by new_id rows between unbounded preceding and unbounded following) as "Average", Count(new_id) over(order by new_id rows between unbounded preceding and unbounded following) as "Count", MIN(new_id) over(order by new_id rows between unbounded preceding and unbounded following) as "Minimum", MAX(new_id) over(order by new_id rows between unbounded preceding and unbounded following) as "Maximum" FROM [practice_sd].[dbo].[windows function]

@surajsidar3280 2 ай бұрын

Can you explain what is use of above query? What is ROWS clause

@shraddhadhakad1154 8 ай бұрын

SELECT new_id,new_cat, SUM(new_id) over(partition by new_cat order by new_id) as "Total", AVG(new_id) over(partition by new_cat order by new_id) as "Average", COUNT(new_id) over(partition by new_cat order by new_id) as "Count", MIN(new_id) over(partition by new_cat order by new_id) as "Minimum", MAX(new_id) over(partition by new_cat order by new_id) as "Maximum" FROM [practice_sd].[dbo].[windows function]

@dipanjansamanta111 2 ай бұрын

Bro full program do piz with schema

@sunilrao6090 2 ай бұрын

Big fan of you sir,

@Deepakyadav-dn1gg 11 ай бұрын

13:23 Ijjat se chalta hai dense rank.........waaah rishabh bhai

@pavan.avasthi 10 ай бұрын

Thank you so much sir ❤

@RishabhMishraOfficial 10 ай бұрын

Happy to help ✅️

@user-fi7zr2eq7h 7 ай бұрын

Bahot achese sikhate ho

@pragati_5_8 2 ай бұрын

u R amazing

@hamza_Techy Жыл бұрын

Great

@RishabhMishraOfficial Жыл бұрын

Glad you liked ✅️

@hamza_Techy Жыл бұрын

@@RishabhMishraOfficial Thanks

@simranjeetsingh4101 Жыл бұрын

Thanku sir.

@adwaydandwate 3 ай бұрын

Hi Rishabh, I ran below queries however the output is same for both. Could you please check and explain once pls. Thanks! select new_id, first_value(new_id) over(order by new_id) First_Value, last_value(new_id) over(order by new_id) Last_Value, Lead(new_id) over(order by new_id) Lead, Lag(new_id) over(order by new_id) Lag from test_data; --WITH RBUPAUF (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) select new_id, first_value(new_id) over(order by new_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) First_Value, last_value(new_id) over(order by new_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Last_Value, Lead(new_id) over(order by new_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Lead, Lag(new_id) over(order by new_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Lag from test_data;

@dinkarjha4500 Жыл бұрын

Hi Risabh your first Querry after running in PostgreSQL Result showing different as per the Pdf sheet select * from test_dat select new_id, new_cat, sum(new_id) over(partition by new_cat order by new_id)as total from test_dat new_id new_cat Total 100 Agni 100 200 Agni 300 700 Dhart 700 500 Dharti 500 200 Vayu 200 300 Vayu 500 500 Vayu 1000

@dinkarjha4500 Жыл бұрын

As i observed should be new_cat in place of new_id

@realfacts3517 Жыл бұрын

Same

@RashidKhan-tz5ct Жыл бұрын

@@dinkarjha4500 Absolutely right

@poojamankotia7353 Жыл бұрын

@@dinkarjha4500 I am having the same results, what changes did you make?

@dinkarjha4500 Жыл бұрын

@@poojamankotia7353 compare shared query with pdf shared by Rishabh and result. partition by and order by both should be in new_cat in place of new_id.

@shwetarawat4027 Жыл бұрын

very helpful

@shraddhadhakad1154 8 ай бұрын

SELECT new_id, ROW_NUMBER() over(order by new_id) as "Row_Number", RANK() over(order by new_id) as "Rank", DENSE_RANK() over(order by new_id) as "Dense_Rank", PERCENT_RANK() over(order by new_id) as "Percent_Rank" FROM [practice_sd].[dbo].[windows function]

@rahulrahul6259 Жыл бұрын

Thanks

@ss8273 10 ай бұрын

superb❤❤❤❤

@RishabhMishraOfficial 10 ай бұрын

✅️

@ganeshn3800 Жыл бұрын

we can use group by instead of partition by, is there any specific difference between these 2?

@sarjakmaniar369 Жыл бұрын

Dense Rank - Magar yeh ijjat se chalta hai 😂 Loved the way of your teaching!! Thank you :)

@RishabhMishraOfficial Жыл бұрын

Glad you liked ✅️

@rajkumarkori7888 5 ай бұрын

your explaination is good. however, you need to write the Query insted of directly pasting. if you write the query i belive your views will increase. thank you.

@ysc7068 5 ай бұрын

Very useful 👌👌

@Prernajain2730 11 ай бұрын

CREATE TABLE LEADLEGG (NEW_ID INT); INSERT INTO LEADLEGG VALUES (100), (200), (200), (300), (500), (500), (700); SELECT * FROM LEADLEGG; SELECT NEW_ID, LEAD(NEW_ID,2) OVER (ORDER BY NEW_ID) AS LEADID, LAG(NEW_ID,2) OVER (ORDER BY NEW_ID) AS LEADIDS FROM LEADLEGG;

@sheetalmishra1258 9 ай бұрын

Please explain partion by function

@vaibhavpatharkar6794 Жыл бұрын

Bahut hi aaacha padhate ho aaap. U earned a subscriber and a like. Aaaap apna course shuru karo, minimal fees like 200 or 300 rakhlo. Aaur pura detail mein sql lo. Kyunki market mein log short course le rahe hai. Aur pura 6 ghanta le rahe hai, lekin usme 4 ghante ki advertisement kar rahe hai aur baaate kar rahe hai. Lekin 4 ghanta nahi padha rahe , sirf 2 ghanta pada rahe hai, masterclass mein. Uske wo 400 rs le rahe. Aur lakhon kama rahe , aur baccho se gaddari kar rahe hai. Aaap toh acche insan ho acche teacher ho , aaap masterclass lo , aur 6 ghanta mein sql khatam kar do. Let me add 1ce again , you teach very lucidly, especially the hindi language

@shubhambali4855 7 ай бұрын

Please provide use case situations along with solution

@vishalchauhan9832 Жыл бұрын

It would be great if u could upload a dedicated video for partition by.

@RishabhMishraOfficial Жыл бұрын

Okayy noted ✅️

@aamirzaman8310 2 ай бұрын

very nice rishabh, itni mehnat karte ho to insert statement bhi de diya karo bhai :p 😜

@kapilkumarkol1547 8 ай бұрын

assignment ans SELECT new_id, LEAD(new_id,2) OVER(ORDER BY new_id) AS "LEAD", LAG(new_id,2) OVER(ORDER BY new_id) AS "LAG" FROM testdata ;

@abhinavbhoyar11 Жыл бұрын

I think it should be 'order by new_cat' instead of 'order by new_id' at 06:04 otherwise result will not be the same as shown.

@amrit_raj_dubey Жыл бұрын

Even I have commented same now 😂

@divakarsaini9849 Жыл бұрын

Thanks for the video

@shubhu3533 3 ай бұрын

bro informative video, but can you share test data file used in video

@rishabhbisht8717 Күн бұрын

Solution for the Question asked: SELECT input_id, LAG(input_id, 2) OVER (ORDER BY input_id) AS lag, LEAD(input_id, 2) OVER (ORDER BY input_id) AS lead FROM your_table;

@RishabhMishraOfficial 22 сағат бұрын

✅️

@rithikahuja8203 Жыл бұрын

Thank you so much rishabh bhaiya for giving such a beautiful content ❤❤

@user-ky1zu1wz8d 11 ай бұрын

SELECT NEW_ID, LEAD(NEW_ID,2)OVER(ORDER BY NEW_ID) AS LEAD, LAG(NEW_ID,2)OVER(ORDER BY NEW_ID) AS LAG FROM TEST_DATA;

@sridharmurari3007 9 ай бұрын

one basic q if not silly : in Rank() > is it applied on partition by col or order by col : while assign for duplicates does it look at partition by or order by data thx