SQL Server: SELECT TOP Statement (TOP n th Position) by using WITH TIES

SELECT TOP 8000 WITH TIES  *
FROM [Hall_Info].[dbo].[ug_applicant_general_info]
ORDER BY HSC_TOTAL DESC, HSC_MATH_GP DESC, HSC_PHY_GP DESC, HSC_CHEM_GP DESC, HSC_ENG_GP DESC, SSC_GPA_MARKS_OBTAINED DESC

[Curtsy] Original Post: http://www.techonthenet.com/sql_server/select_top.php

SQL Server: SELECT TOP Statement

This SQL Server tutorial explains how to use the SELECT TOP statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) SELECT TOP statement is used to retrieve records from one or more tables in SQL Server and limit the number of records returned based on a fixed value or percentage.

Syntax

The syntax for the SELECT TOP statement in SQL Server (Transact-SQL) is:

SELECT TOP (top_value) [ PERCENT ] [ WITH TIES ]
expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];

Parameters or Arguments

TOP (top_value)
Returns the top number of rows in the result set based on top_value. For example, TOP(10) would return the top 10 rows from the full result set.
PERCENT
Optional. If PERCENT is specified, then the top rows are based on a percentage of the total result set (as specfied by the top_value). For example, TOP(10) PERCENT would return the top 10% of the full result set.
WITH TIES
Optional. If the WITH TIES clause is specified, then rows tied in last place within the limited result set are returned. This may result in more rows be returned than the TOP parameter permits.
expressions
The columns or calculations that you wish to retrieve.
tables
The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
WHERE conditions
Optional. The conditions that must be met for the records to be selected.
ORDER BY expression
Optional. It is used in the SELECT TOP statement so that you can order the results and target those records that you wish to return. ASC is ascending order and DESC is descending order.

Example – Using TOP keyword

Let’s look at a SQL Server example, where we use the TOP keyword in the SELECT statement.

For example:

SELECT TOP(5)
employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Anderson'
ORDER BY employee_id;

This SQL Server SELECT TOP example would select the first 5 records from the employees table where the last_name is ‘Anderson’. If there are other records in the employees table that have a last_name of ‘Anderson’, they will not be returned by the SELECT statement.

You could modify this example to include the WITH TIES clause as follows:

SELECT TOP(5) WITH TIES
employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Anderson'
ORDER BY employee_id;

The WITH TIES clause would include rows that may be tied in last place within the limited result set. So if the 5th top record is a tie, then all records in this tie position would be returned by the SELECT TOP statement. This will result in more than 5 records being returned.

Example – Using TOP PERCENT keyword

Let’s look at a SQL Server example, where we use the TOP PERCENT keyword in the SELECT statement.

For example:

SELECT TOP(10) PERCENT
employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Anderson'
ORDER BY employee_id;

This SQL Server SELECT TOP example would select the first 10% of the records from the full result set. So in this example, the SELECT statement would return the top 10% of records from the employees table where the last_name is ‘Anderson’. The other 90% of the result set would not be returned by the SELECT statement.

You could modify this example to include the WITH TIES clause as follows:

SELECT TOP(10) PERCENT WITH TIES
employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Anderson'
ORDER BY employee_id;

The WITH TIES clause would include rows that may be tied in last place within the limited result set. So if the last position in the SELECT TOP(10) PERCENT record set is a tie, then these tied records would be returned by the SELECT TOP statement. This will result in more than 10% of the full record set being returned.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s