selecting top exam results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

selecting top exam results

Author

Topic

Tshepang
Starting Member

4 Posts

Posted - 2009-07-13 : 10:43:34

Hi,

I'm trying to make a query that will select the top 100 students in terms of marks from an exam written. Tried subqueries to order the data but SQL server 2009 tells met that you cant have an ORDER BY clause in a sub query does anyone have an idea of how to do this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-13 : 10:55:20

Can you poste the code you used?

Madhivanan

Failing to plan is Planning to fail

Tshepang
Starting Member

4 Posts

Posted - 2009-07-13 : 11:12:23

Here is what I have tried, I'm using SQL server 2005

SELECT TOP 10 StudentID, StudentName, StudentSurname, EntranceExamMark
FROM Student

Where (Select StudentID, StudentName, StudentSurname, EntranceExamMark from student
ORDER BY EntranceExamMark DESC)
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-13 : 13:40:09

quote:
Originally posted by Tshepang

Here is what I have tried, I'm using SQL server 2005

SELECT TOP 100 StudentID, StudentName, StudentSurname, EntranceExamMark
FROM Student
ORDER BY EntranceExamMark DESC
thanks

Tshepang
Starting Member

4 Posts

Posted - 2009-07-14 : 11:17:11

Thanks but I had already tried that query. The TOP function merely takes the top 'n' number or rows in a table. to give you an example the table has several marks 50, 20, 30,80,98,40, 48 and 60. if you used the "top" function for the top 3 it would show 50, 20 and 30 but I need the query to show the records of the students who got 98, 80 and 60 which are the highest numbers in the sequence.

Help still needed.

vani_r14
Starting Member

24 Posts

Posted - 2009-07-14 : 19:09:24

Hi

The last time i had a similar issue - I used a case statement pulled out all the rows and inserted them into a temp table and then selected the data from the temp table. try that... it is a bit complicated but works effectively

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources