I Stopped Using SQL Window Functions… Instead I Use This SQL Function

Added: 2026-05-11

[00:00:01]Hello everyone, welcome back to the channel. So today we are going to talk about a very interesting SQL function and it will solve a lot of problems that can be solved using window functions, right? And this is not a window function by the way. It is not a qualify if you are thinking right. It's a different function and you would not have heard about it because it is not available in general databases like MySQL, Postgress or SQL server, right? But it is available in most of the modern analytics data engineering platforms like uh datab bricks is no flag athena duck db all those kinds of modern systems it is available right so we will go in depth understanding what problems it will solve how it can be used to reduce your queries to reduce your subqueries and we will understand the different variations of it there are multiple variations of this function and we will understand all of that step by step. Right? So we will start with the problem statement and then we will see how this solves this.

[00:01:06]Uh so let's assume we have this data.

[00:01:09]Right? So we have a employee table with employee ID, department salary.

[00:01:14]Now my problem statement is simple that I want the employee ID with the highest salary. Right? So simply how can I do it? I can say order by salary descending right and say limit one. So this is one way of achieving it right. So it will give me uh this person 20,000 salary and employee ID six. Okay. So this is one way of solving this problem. Now let's say now my question is I don't want just the employee highest salaried employee. I want highest salaried employee in each department. Right? So in sales who is the highest salaried employee and in purchase. So for that we have to use the low number. So we have to say low number over partition by partition by department and order by salary descending. Right.

[00:02:23]So it will give me salary uh rank one for highest salaried employee in each department. Right? So this guy got to 20,000 and this guy got 10,00011.

[00:02:36]Right? And then I can say select a star from this where are then equal to one.

[00:02:40]So I can create a subquery and do it right that that you understand right.

[00:02:45]Okay. Now what we will do we will try to solve these two problems using max by function. Right? So let me explain what is max by function. I'll just select from employees and here I will say max by and similar to max by we have miny. So you if you understand max by you will understand minby as well. So now what we have to do in max by it takes two argument. First argument is what value you want. So in my case I want employee ID. So I will say employee ID I want right now based on which value. So I want based on salary.

[00:03:30]So what I'm saying here give me employee ID who has the highest salary. Right? So give me employee ID filter by the maximum salary. This is how you have to read. Right? Give me the employee ID filter by the maximum salary. So the employee with the highest salary for that I need employee ID right see simply if you use max right max will give you highest salary right it will not give you employee ID right you will have to use subquery and get it right so max will give you maximum salary that it is 20,000 if you do employee ID it will give you maximum employee ID right but what maxi is doing it is taking two arguments Right? It is saying that okay give me employee ID but based on max salary. This is how you have to understand right employee best uh high salary right. So what what we should go get in this case we should get this guy right employee ID six. So let's see let me run this.

[00:04:48]See we are getting six right. Great. Now let's say my next requirement is I want this max salary or sorry employee ID of maximum salary within each department right so I want to perform this within each department the way we did using row number so it is simple I will just say give me department uh what is this department yeah department comma okay and I will do group by so this is a kind of a aggregate function this is not a window function right this is not a window function this is a aggregate function right so I'm saying group the data based on department so it will be grouped and within each group give me employee ID of employee with highest salary right so this is the second variation let me done So now it is saying in sales the employee ID 1 right employee ID 1 has the highest L you see 8,000 9,000 10,000 and then in purchase department highest L is employee ID 6 see 20,000 right so this is working fine okay great now let's move forward now what I want right this is fine I'm writing department name as well as the employee with highest salary. Now what I'm saying within this give me the value. So I want all the rows and one more column you give me employee with highest salary. So here we will get let's say for full table not department wise. So full table if you see six. So I want six six66.

[00:06:40]This is how I want to achieve it. So it is simple. I just have to say over full table like write the window function right so I'll just remove it and I will say star comma this right and let me run this now now group by is not required okay now if you see I'm getting 666 so what it is doing it is doing over full table right and getting the employee ID with highest value which is six. Now here I can say partition by partition by uh partition by department.

[00:07:21]If I do now within each department it will get it and give it to me.

[00:07:29]So if you see 111 for sales and 666 for purchase okay I hope this makes sense. Now let's take a very important thing here that what if there is a tie right what if there is a tie let me insert one more record in this table so let me take this and I will say for department uh uh employee ID let's say 8 for purchase right for purchase and Let's keep it 20,000. So, one guy is already there with 20,000. I'm inserting one more.

[00:08:14]Okay.

[00:08:15]Now, let's see what it will do.

[00:08:18]Right. So, now what it will do? What it will give? It will give me six or it will give me eight because employee 8 also has 20,000. Right? So, let's run this.

[00:08:31]So, now if you see it is giving me six, right? And now you will say why it is not giving it because both have 20,000 right? So what datab bricks documentation says or snowflake documentation says that in case of tai it is nondeterministic which means it can give six or it can give eight. You cannot guarantee anything. Right? In case of tie it can sometimes give six it can sometimes give eight. It is nondeterministic.

[00:08:59]Now if I want to make sure that in case of tie I want to pick some particular value let's say based on employee ID right so let's say my requirement is if there is a tie get the lowest employee ID or highest employee ID right so let's see how we can solve it now with row number we could have solved it using uh order wise comma employee ID right so it will break the tie in this case also similar kind of thing can be done so what we will do we will Just say uh let me remove this for now or let it be let it be. So I will say max by employee id salary but here I will say strruct right. So I will pass two values here.

[00:09:46]See it takes two arguments. So I cannot pass two argument but I can pass a strct. Ststruct means more than two values I can pass in this. It's a structure right? as a salary and employee ID.

[00:10:00]Right? Now what it will do? It will give me max by max value of employee ID based on salary maximum salary. If the salary is a tie then it will take within that salaries within those rows maximum employee ID. So now we will get eight instead of six. Right? If I done this now if you see we are getting it right because I'm saying max value of salary if there's a tie then use employee right now let's say your requirement is that you don't want maximum salary I mean you want maximum salary but in case of Tai you want the employee ID with lower employee ID right so you can use minus So min -6 will be greater than minus 8 in this case. So this is kind of work around.

[00:10:57]So now if you see we are getting six right. So when we negate the value it becomes reverse. Okay. Now similarly there is a min by function obviously right. And if I done this I will get employee with lowest salary. So lowest salary is three. If you see and here lowest salary is five which is null right. So it will consider null as well which is the lowest value.

[00:11:25]Right?

[00:11:27]Great. I hope this makes sense. Okay.

[00:11:30]Wait. In case of min, let me remove this. Let me check with salary what it will do. I think with null there should be something else. It should not consider. I'm not sure. Let's see. So I'm just doing simply salary. Let's see what it will do.

[00:11:48]Okay. Okay, so it is giving me seven, right? So it is ignoring the null value, right? So 15,000 20,000 8,000 20,000. So this guy has the lowest salary mean by so we are getting seven. Now when we were doing this, right? What it was doing? It is checking salary first, right? And if salary is null, then it is checking the employee ID, right? So if I run it again right so null is what five employee ID right so that's why it is giving me uh this right so lowest is in this case salary lowest is uh 8,000 and null right and then after that okay why it is giving me five let's try to understand right so I'm saying Distract salary right salary is null. So if salary is not there it will check the employee ID. Okay sal is null then it will check the employee ID. So it should give me four isn't it?

[00:13:00]So why it is giving me five. Okay. So let me know in the comment section if you find out. I'm not sure why it is giving me five. We have to check what could be the reason because what I'm thinking is it will be based on salary first. So salary is null here. So it should ignore it completely right in the other case. But it is not ignoring here.

[00:13:22]Okay. So because if we just check employee ID then it should give me four right. But there is some other behavior you would let me know in the comment section. I will also figure out and let you know in the comments later. But I hope you understand how this all of this is working. Okay, great. Thanks for watching. Uh have a good day and please do like the video and share with your friends.

[00:13:48]Thank you for watching this video till here and if you really like the video, please make sure you hit the like button because it really motivates me to create more powerful and strong content for all of you. Also I see a 50% of my viewers are not the subscriber of this channel.

[00:14:05]So please subscribe because as more subscribers come I feel more motivated.

[00:14:10]I feel more energy to create more content. Right? So please support me like the video. Share with your friends.

[00:14:17]Subscribe to the channel and keep learning. Keep going. Thank you. Have a good day.