Monday, March 13, 2017

SQL SELECT TOP

In real life, we may came across a situation where we require to find the top 3 scorers in a game, top 5 students in a class, etc. To deal with these kinds of situations, SQL Server has a feature to select the TOP n rows from a table. Users can retrieve the TOP n data records from the database table using the TOP clause. It is used to access and retrieve the TOP n data rows from a table in the database.

The SQL TOP clause is used as a row limiter to limit the records in a result-set. It can be used to fetch a TOP N number or X percent records from a table.





The SELECT TOP clause is very useful when dealing with databases having thousands of records in the tables since returning large result-sets may hamper the performance.


Syntax of SELECT TOP:
           
     
      SELECT TOP number [percent] select_column_list
      FROM TABLE_NAME
      [CLAUSE(S)];

[] = Optional code

TOP number returns the number of rows in the result-set based on number specified.

percent is optional. It returns the percentage of rows in the result-set based on number specified.

select_column_list is the list of columns for which the data is to be listed.

FROM specifies the location (TABLE_NAME i.e. database table) from where the data is retrieved.

CLAUSE(S) specifies the search conditions for the rows returned by the query. It may include following clauses:

·         WHERE clause
·         ORDER BY clause

When TOP is used along with ORDER BY clause, the result-set contains the first N number or X percent of ordered records, otherwise it returns the records in an undefined order.

Features of TOP:
  
·         TOP retrieves the TOP N records from a table without using WHERE clause.

·         It can also work with UPDATE and DELETE statements.
·         User can specify “number of rows” or “percentage of rows” after TOP keyword.
Share This
Previous Post
Next Post

TekGrabs, established in 2017, is in the business of sharing Information. It provides news, articles and photos about the different technologies, inventions in different domains. Provides information on programming and databases.

0 comments: