How To Use Select Top Clause In Mssql

Hello Friend Today In This Blog I Will Show You How To Use Select Top Clause In Mssql.The SELECT TOP statement returns a specified number of records.SELECT TOP is useful when working with very large datasets.Non SQL Server databases use keywords like LIMIT, OFFSET, and ROWNUM.The SQL SELECT TOP statement is used to retrieve records from one or more tables in a database and limit the number of records returned based on a fixed value or percentage.This SQL tutorial explains how to use the SQL SELECT TOP statement with syntax and examples.The SELECT TOP clause is used to return the top X numbers or N Percent row from the table. Only MSSQL server and MS Access database support the SELECT TOP clause.To fetch limited number of records, LIMIT clause is used in MySQL database & ROWNUM in Oracle database. You can specify “number of rows” or “percentage of rows” after TOP keyword.The SQL TOP clause is used to fetch a TOP N number or X percent records from a table.The SQL SELECT TOP Statement is used to select top data from a table. The top clause specifies that how many rows are returned.The SELECT TOP clause is used to specify the number of records to return.The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact on performance.There are many good uses of the SELECT TOP 1 method of querying. Essentially, the select top 1 method is used to find the min or max record for a particular column’s value. There is some debate as to whether this is the ‘correct’ method of querying, however it should be known that this method does not break any guidelines and is supported by all standards of SQL.

The TOP 1 means to only return one record as the result set. which record is returned, depends on the column that is specified in the order by clause. If you want to find the record with the minimum value for a particular column, you would query the record with the ORDER BY being ascending (ASC). If you want to find the maximum record with that value, you would query it with the ORDER BY descending (DESC).One of the major differences is that the SELECT..TOP 1 will only ever return one record. Where as joining against the max has a possibility of returning more than one record. This is very important when setting variable in SQL Server.

SQL Server / MS Access Syntax:

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition; 

Here Is An Example Related To Topic

Step 1 :Create database

Create database BMC

Step 2:Create table

Here In This step We Will Create table.

Here In This Step We Required Four Column With Name Cids ,Cname ,Ccity And CPrice using Parameter bigint,varchar and float.

create table Client_table_news
(
Cids bigint,
Cname varchar(50),
Ccity varchar(200),
CPrice float
);

Step 3 :Create Insert Trigger Functionality

Here In This Step We Will Insert Data Using Insert Command.

Data Will Be Added Manually Using Insert Command.

Insert Into Client_table_news values(1,'VIRAJ','MUMBAI',15000.00)
Insert Into Client_table_news values(2,'RAJ','DELHI',10000.00)
Insert Into Client_table_news values(3,'ANIL','PUNE',12000.00)
Insert Into Client_table_news values(4,'SUNIL','MUMBAI',9000.00)
Insert Into Client_table_news values(5,'SHERYAS','JAIPUR',11000.00)
Insert Into Client_table_news values(6,'AJAY','MUMBAI',16000.00)

select *from Client_table_news

Step 4 :Create TOP,LIMIT and ROWNUM Functionality 

Here In This Step We Will Create TOP,LIMIT and ROWNUM Functionality.


TOP:

SELECT TOP 3 * FROM Client_table_news;

LIMIT :

SELECT * FROM Client_table_news

LIMIT 3;

ROWNUM :

SELECT * FROM Client_table_news

WHERE ROWNUM <= 3;

SQL TOP PERCENT Example


The following SQL statement selects the first 50% of the records from the "Client_table_news" table:

SELECT TOP 50 PERCENT * FROM Client_table_news;