00:01
Right, so today we are going to be writing a query to find top 10 students, including students tied for the final spot, even if that results in more than 10 students.
00:11
Now, i'm just going to be using postgres because it wasn't specified which flavor of sql that i should use.
00:19
So i'm just going to use the open source one.
00:21
And even though the question says using sql ranking, i believe this is a bit ambiguous.
00:31
Here because as you'll see in a second there are actually two types of ranking in sql so let's just start i'm sure you can agree that this is the table s or what i've assumed it to be we have a student a subject and marks right and i've created a bunch of students which you can see here i've created an bob charles and darren with different scores bob and charles are tied for the same total marks even though they have different marks in each subject.
01:03
And darren has a perfect 50 -50 and is going to be our a -star student.
01:07
So even though the question asked to rank the top 10, i believe i can demonstrate the same point just by creating four students and ranking the top three.
01:15
So let's explore the query here.
01:18
So those are just the insert statements.
01:19
You don't have to worry about that.
01:21
I printed the entire table out for you to see.
01:24
And so the answer here is going to use a sub -quiry.
01:28
If you are not sure of what a sub -quiry is, it's basically where you have to create a smaller query and then run another query on that.
01:37
So i've chosen to go with this syntax, which is called a common table expression, meaning this inner table here, select student, blah blah, blah, is going to be henceforth known as student ranking, which is used in the final query here.
01:54
So let's go over this.
01:56
We select student and we are going to sum marks grouped by students...