Next up, we're going to talk about first value.
First value, according to the docs returns the value of whatever expression is in the parentheses from
the first row of the window frame.
So when we have these different windows within each one of them, they'll be in some order, right?
We can order them by whatever we want using order by instead of over.
And then we can ask for the first value of some particular column from that first row.
So this is how we could do something like let's take our basic employee number department salary that
we're selecting and then do something like first value employee ID.
Over.
And then I'll say sort of by salary, I guess, descending.
So what this is going to do is divide it just like over always does divide up all the rows in our case
into one window.
There's no partitioning.
So one window with all the rows order them by salary.
So the first row is going to have the highest salary.
And then what we're saying is we want the employee ID So I could put something else in there too.
You know, this is how I could get the last name of the highest paid person in each department or something
like that.
What we're doing right now, though, since we don't have names, it's just getting the employee number
of the highest paid employee.
Let's paste this and Oh, geez, it's EMP number.
No, not ID.
And now you'll see that we have ten in this column.
I didn't give it a name, so it's real long.
But every single row just says ten, ten, ten, ten, ten.
And that's because that is the employee number of the row that has the highest salary because we ordered
by salary within our one massive window descending, so highest to lowest.
And then we took the first one of those rows and got the value of employee number.
So here's another version of that where we partition.
Let's do first value.
Get the employee number where we partition by department.
The apartment.
And we'll order by salary descending again within each department.
And let's call this one as highest.
Paid in dept and this one will be as highest paid overall.
So this is going to give us the employee number of that highest paid person.
And you'll see within highest paid department we now see.
All right.
Within the sales department, the highest paid person has idea of ten employee number ten.
That makes sense.
159,000 within engineering, the highest paid person that has the ID of four or employee number.
I keep saying ID, but it's employee number four, which is 103,000.
And then we have to go to customer service.
The highest paid person has ID of 17 because employee number 17 right there, 61,000.
So to recap how that works, once we have our over and whatever partition by may or may not be there,
we have one window or maybe multiple windows and then within those windows the rows can be ordered.
And then once that's done, once the splitting into windows is done and they're all ordered within each
of those windows were then taking from the first row the employee number.
But of course you could put anything else in here.
If you had more complicated data, you could put whatever you want in there, some expression that is
based on that first row within each window.
We also have last value, same thing.
It's just going to work the other way around.
And we have nth value where you can specify after the expression you want, the second or the third,
the fifth, the 90th, whatever you want, the nth row of that window frame.
Take the expression from that row so I won't waste your time and show an example of that.
It's the same exact concept, though.
You just have a different number, a different position you're looking for.