TOP in SELECT MSSQL query
|We can collect some records from a large number or records of a MSSQL table by using TOP clause in our query. We have to specify the field where we want to apply the TOP command and number of records we want to retrieve.
We will apply TOP command to our student table. (You can download the data and structure of the table at our SQL SELECT section.) . Using this command we can collect first n records ( say 5 here ) from the table. Here is the query.
rs1.open " select TOP 5 class, name,sex,mark from student " , conn
Note that the above query is to be applied to the script we have written in our SQL select section. You have to replace the above query line in appropriate place. ( we are not repeating the same script again here ) .
The command TOP only searches for 5 records and it does not go in any order. Let us try to collect to 10 students based on the mark they have secured. Here we will use another sql command ORDER BY ( Read more on ORDER BY command in our SQL section ) . Here is the command.
rs1.open " select TOP 5 class, name,sex,mark from student ORDER BY mark " , conn
The above query will return 5 lowest mark records. By default the order by clause return in ascending order so to change that we have to add DESC to the query to get top 5 rank holders in student table.
rs1.open " select TOP 5 class, name,sex,mark from student ORDER BY mark DESC " , conn
You can read more on order by command in our sql section.