/* 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

/* 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