# question

## Could someone please tell me how can I get the result?

![alt text][1] [1]: /storage/temp/3576-sql-question.jpg
sql-question.jpg (66.6 KiB)

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
What have you tried? Please show us.
1 Like 1 ·
·
When you **do** try, please keep in mind that this is a tricky question because it is not asking you to write a query returning first 10 students (10 rows) based on the scores, but is asking for top 10 scores, which might return more than 10 students if the tenth score is secured by more than one student. For example, suppose that 3 students secured a score of 95, 2 students - 92, 7 students - 90, 5 - 89, etc. In this case 3 students with 95 and 2 students with 92 make up the first 5 and the next 5 of 7 with 90 fill the first 10, so there are actually 12 students with top 10 scores, and therefore, your query should return 12 records in this case.
1 Like 1 ·
·
Homework, test or an interview question? Regardless of the answer you are the one who needs to be able to answer this. And consider what Oleg stated, it is important.
0 Likes 0 ·
·
I have tried the following code. Please do verify and inform me if this is the correct answer. Thank you for your reply. select s.*,r.score,r.feedback from t_students s,t_results r where s.id=r.student_id order by r.score desc limit 10;
0 Likes 0 ·

·
I have tried the following code. Please do verify and inform me if this is the correct answer. Thank you for your reply. SELECT s.* , r.score , r.feedback FROM t_students s , t_results r WHERE s.id = r.student_id ORDER BY r.score DESC limit 10

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
What did you get when you ran this? Did it provide the results you were looking for?
1 Like 1 ·
·
It might be the correct results but the join style is REALLY out of date. You are using the ANSI-89 style join instead of the ANSI-92 style join. It has been almost 30 years since a comma separated list of tables was the best way to do that. http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/08/bad-habits-to-kick-using-old-style-joins.aspx
1 Like 1 ·
·
Certainly looks like MySQL with that LIMIT 10.
1 Like 1 ·
·
Agreed. I just wanted to clarify so I knew what we were working with. ;-)
1 Like 1 ·
·
Also, is this for MySQL or SQL Server?
0 Likes 0 ·
The fact that you have tried something makes a big difference. Nobody on this site has objections to answering homework/test questions per se, but the attempt to find a solution first, not just asking someone to do their homework is essential. The reason is that when someone tries first, and then learns by reading correct answer after the attempts to solve the problem are exhausted and the solution is still not found, then the acquired knowledge actually sticks. In your case, there are 2 problems with your query: 1. It will not execute because the syntax is invalid (there is no such a thing as "limit" in T-SQL) 2. It uses old syntax with comma-delimited list of tables, which is ill advised, and should be restated as a join. Correctly restated query should be something like this:
```select
top 10 with ties
s.* , r.score, r.feedback
from t_students s inner join t_results r
on s.id = r.student_id
order by r.score desc;
```
The **select** part of the query is the last step in execution order (even though the select statement begins with the word select). The step before last is the **order by**, this is the one which sorts the data so that the rows listing the students with higher scores are moved to the top. The **top 10** part limits the results to include only first 10 rows or more than 10 in case if more than one row is tied for the tenth place (this is achieved by adding **with ties** to the [top][1] clause. Certainly, it would be improper to not include the students which are tied for the 10th place if the there is more than one such student. In order to see how it works, lets expand the question to also include the students' rank based on their scores. To do this you can use the windowing function which is quite appropriately named [rank][2]. Objectively, this makes the query slightly more complicated because we have to rank all rows first and only then select the part of the data we need based on the criteria of choosing first 10 by rank. This will require us to use either subselect or the [CTE][3]. Here is the query with CTE: ;with data as ( select s.* , r.score, r.feedback, rank() over (order by r.score desc) RankByScore from t_students s inner join t_results r on s.id = r.student_id ) select * from data where RankByScore <= 10; Running either query will produce the same rows, but the second query will also include the rank information. Using the example from my comment, first 3 students with 95 score will get the rank = 1, next 2 with 92 score will get the rank 4, and next 5 students with 90 score will get the rank = 6. The students below that score will be ranked 13 and higher. Please read the information about **top**, **rank** and **CTE** which are hyperlinked in this answer to have a complete understanding of the question and its solution. Oleg [1]: https://msdn.microsoft.com/en-us/library/ms189463(v=sql.110).aspx [2]: https://msdn.microsoft.com/en-us/library/ms176102(v=sql.110).aspx [3]: https://msdn.microsoft.com/en-us/library/ms175972(v=sql.110).aspx

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.