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] 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]. 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]. 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 : https://msdn.microsoft.com/en-us/library/ms189463(v=sql.110).aspx : https://msdn.microsoft.com/en-us/library/ms176102(v=sql.110).aspx : https://msdn.microsoft.com/en-us/library/ms175972(v=sql.110).aspx
20 People are following this question.