SELECT TOP PERCENT – SQL Server Syntax Example: TOP PERCENT – T-SQL Example

Get 5 Hours of FREE PREMIUM Videos:

LearnVisualStudio.NET Free Preview

LearnVisualStudio Premium Videos

dotNetVideos Premium Video Catalog

SELECT TOP PERCENT – SQL Server Syntax Example: TOP PERCENT – T-SQL Example

Purpose: – Illustrates the SQL Server syntax for TOP PERCENT.

SYNTAX:

SELECT [ ALL | DISTINCT ]
[ [TOP (expression) [PERCENT] [ WITH TIES ] ]
column_list
[ INTO new_table ]
[ FROM { table_source } [ ,...n ] ]
[ WHERE select_condition]
[ GROUP BY ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ]

NOTES: Expression is the numeric expression that specifies the number of rows to be returned. Expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.

Parentheses that delimit expression in TOP is required in INSERT, UPDATE, MERGE, and DELETE statements.

If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.

TOP…WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified.

TOP cannot be used together with UPDATE and DELETE statements on partitioned views.

PURPOSE:
Top specifies that only the top number of rows or top percent of rows will be returned in the result set. The TOP expression can be used in SELECT, INSERT, UPDATE, MERGE, and DELETE statements.

 

Code Sample for SELECT TOP PERCENT:

 T-SQL | 
 copy code |
?
 
 
/* TOP example from http://idealprogrammer.com 
 
 PURPOSE: Top specifies that only the top number of rows or top percent of rows will be returned
          in the result set. The TOP expression can be used in SELECT, INSERT, UPDATE, MERGE, and 
          DELETE statements.
          
          
 
 SYNTAX: 
 
 
 SELECT [ ALL | DISTINCT ] 
			[TOP ( expression ) [PERCENT] [ WITH TIES ] ] 
			 column_list  			
			[ INTO new_table ] 
			[ FROM { table_source } [ ,...n ] ] 
			[ WHERE select_condition] 
			[ GROUP BY ] 
			[ HAVING  search_condition  ] 
			[ ORDER BY  order_expression [ ASC | DESC ] ] 
 
   NOTES: Expression is the numeric expression that specifies the number of rows to be returned. 
   Expression is implicitly converted to a float value if PERCENT is specified; otherwise, 
   it is converted to bigint.
 
   Parentheses that delimit expression in TOP is required in INSERT, UPDATE, MERGE, and DELETE 
   statements. 
   
   If the query includes an ORDER BY clause, the first expression rows, or expression  
   percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, 
   the order of the rows is arbitrary. 
   
   TOP...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is 
   specified.
   
   TOP cannot be used together with UPDATE and DELETE statements on partitioned views.
*/
 
 
CREATE TABLE people(
     ID          INT,
     firstname   VARCHAR (20),
     lastname    VARCHAR (20),
     statecode   VARCHAR (2),
     alive       BIT,
     height      INT )
 GO
 
 
 
 
 
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height) 
 VALUES (1,  'Paul', 'Revere', 'AL', 0, 72)
 GO
 
 INSERT INTO people (ID, firstname, lastname, alive, height) -- statecode NULL
 VALUES (2,  'John', 'Lennon',  0, 69)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (3,  'Steve', 'Martin', 'NY', 1, 75)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (4,  'Steve', 'Washington', 'VA', 0, 75)
 GO
 
 
 
 
 
 
 -- 1. Example of using TOP with variables
 
 DECLARE @cnt AS int;
 SELECT @cnt=3;
 
 SELECT TOP(@cnt) 'Example #1' as Example,*
  FROM people p 
 
 GO
 
 
  -- 2. Example of TOP PERCENT WITH TIES
 --    25% would select 1 record without ties, but WITH TIES causes 2 to be selected
 
SELECT TOP(25) PERCENT WITH TIES 'Example #2' as Example, firstname, lastname, height 
  FROM people p
  ORDER BY  firstname DESC
 
 GO
 
 
 
 DROP TABLE people;
 
 GO
 
 

Sample Output for SELECT TOP PERCENT Example

Related posts:

1. SELECT INTO – SQL Server Syntax Example: SELECT INTO – T-SQL Example SELECT INTO - SQL Server Syntax Example: SELECT INTO -...
2. SELECT – SQL Server Syntax Example: SELECT – T-SQL Example SELECT - SQL Server Syntax Example: SELECT - T-SQL Example...
3. ORDER BY – SQL Server Syntax Example: ORDER BY – T-SQL Example ORDER BY - SQL Server Syntax Example: ORDER BY -...
4. HAVING – SQL Server Syntax Example: HAVING – T-SQL Example HAVING - SQL Server Syntax Example: HAVING - T-SQL Example...
5. SELECT CASE – SQL Server Syntax Example: SELECT CASE – T-SQL Example SELECT CASE - SQL Server Syntax Example: SELECT CASE -...

Related posts brought to you by Yet Another Related Posts Plugin.