How to Use the TOP Option in SQL Server
In this tutorial you will learn how to use the TOP option. The TOP option is a exclusive T-SQL feature that allows you to limit the number or percentage of rows that your query returns. A number is specified in the TOP option that declares how many row are displayed.
Setting Up
Before we start on the queries, let’s create a table based on movies. Open SQL Server and click the New Query button. Inside the query we will create a table based on movies that will have a Title, Director, and ReleaseDate.
Since we are going to select three movies with the TOP option we must specify it in the table followed by the columns to be displayed. After we do that we can detail from what table the columns are from, in this case Movies. We enter FROM Movies below the previous statement. and then below the FROM Movies statement we will use the ORDER BY statement to order the titles in descending order.
Thanks for reading and make sure to download the source files to get a better understanding of how the code works.
TopKeyword.zip
Setting Up
Before we start on the queries, let’s create a table based on movies. Open SQL Server and click the New Query button. Inside the query we will create a table based on movies that will have a Title, Director, and ReleaseDate.
Code Block
CreateMovieTable.sqlce
Create the Movie table.CREATE TABLE Movies
(
Title nvarchar(30) not null,
Director nvarchar(30) not null,
ReleaseDate datetime not null
);
Need help with cloud hosting? Try Server Intellect. We used them for our cloud hosting services and we are very happy with the results!
Next we will enter data into the table for us to work with. We will enter multiple values into the table in one query by defining the table and columns with the INSERT statement and then adding movie data with the SELECT statement.Code Block
MovieInsertValues.sqlce
Insert values into the Movies table.
INSERT INTO Movies (Title, Director, ReleaseDate)
SELECT 'Limitless', 'Neil Burger', '20110318 12:00:00:000'
UNION ALL
SELECT 'Source Code', 'Duncan Jones', '20110401 12:00:00:000'
UNION ALL
SELECT 'Insidious', 'James Wan', '20110401 12:00:00:000'
UNION ALL
SELECT 'Hop', 'Tim Hill', '20110401 12:00:00:000'
UNION ALL
SELECT 'Sucker Punch', 'Zack Snyder', '20110325 12:00:00:000'
UNION ALL
SELECT 'The Lincoln Lawyer', 'Brad Furman', '20110318 12:00:00:000'
UNION ALL
SELECT 'Rango', 'Gore Verbinski', '20110304 12:00:00:000'
I just signed up at Server Intellect and couldn’t be more pleased with my fully scalable & redundant cloud hosting! Check it out and see for yourself.
TOP Option KeywordSince we are going to select three movies with the TOP option we must specify it in the table followed by the columns to be displayed. After we do that we can detail from what table the columns are from, in this case Movies. We enter FROM Movies below the previous statement. and then below the FROM Movies statement we will use the ORDER BY statement to order the titles in descending order.
Code Block
TOPoption.sqlce
Top Option query.SELECT TOP (3) Title, Director
FROM Movies
ORDER BY Title DESC;
We used over 10 web hosting companies before we found Server Intellect. Our new server with cloud hosting,was set up in less than 24 hours. We were able to confirm our order over the phone. They responded to our inquiries within an hour. Server Intellect’s customer support and assistance are the best we’ve ever experienced.
Run the query and you should see three titles displayed in descending order.Thanks for reading and make sure to download the source files to get a better understanding of how the code works.
TopKeyword.zip
