ttmind

Main Navigation

ttmind
  • jim-jams
  • Tech
  • Positive
Login

Login

Facebook Google

OR

Remember me Forgot password?

Don't have account? Signup here.

Sort by Categorys

.Net

PHP

Java

JavaScript

Database

Server

Client Side

Tools

Artificial Intelligence

Cloud

Hybrid Development

Event

Smart City

Education

Security

Scrum

Digital Marketing

APP Development

Business

Internet

Simulation

Art

Network

Microservices

Architecture

Technology

Leadership

    Top Articles

  • How Does Social Media Bring People Together?
    TTMind Author
  • How to read appSettings JSON from Class Library in ASP.NET Core
    Anil Shrestha
  • Printing Support In Asp.Net Core
    TTMind Author
  • HOW TO EXTRACT TEXT FROM IMAGE USING JAVASCRIPT (OCR with Tesseract.js)?
    Prakash Pokhrel
  • Images Upload REST API using ASP.NET Core
    Prakash Pokhrel
  • Related Topic

  • Hosting ASP.NET Core Web API on IIS
  • Difference between char, nchar, varchar, and nvarchar in MS SQL Server
  • What's new in SQL Server 2019?
  • Uploading .csv sample table file data to SQL Server
  • SQL Server Stored Procedures: Part-1
  • Tech
  • About Us
  • Contact Us
  • TechHelp
  • PositiveHelp
  • Jim-Jams Help
  • Terms & Conditions

© Copyright ttmind.com

Main Content

Use of TOP and DISTINCT in SQL SERVER

Server SQL Server about 6 years ago || 6/19/2019 || 11.5 K View

  • Hide

TOP and DISTINCT clause.

TOP applies to: SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse and  Parallel Data Warehouse

DISTINT applies to:  SQL Server Analysis Services

TOP Clause:

TOP is mainly used to limit the result of query in terms of number or percentage of the result from database.  It selects result limits to TOP from N numbers of data from ordered rows by using ORDER BY statement otherwise it selects undefined order data. TOP can be used in statements SELECT, UPDATE, DELETE, INSERT, or MERGE statement.

To predict the rows affected can be indicated by using ORDER BY clause with TOP clause which is one of the best practice using TOP clause.

Note: If you don't have database to test then see my previous article. Click here.

Syntax:

SELECT TOP <expression> [PERCENT]
    [WITH TIES]
FROM
    <table_name>
ORDER BY
    <column_name>

Syntax description:

You can use clauses like WHERE, JOIN, HAVING and GROUP BY with SELECT statement.

<expression>

It specifies the number of rows to be returned. <expression> is in float value if [PERCENT] is used otherwise it is in BIGINT.

[PERCENT]

[PERCENT] keyword is used to give N percentage of rows result.

[WITH TIES]

This keyword is used to return addition value that matches limited last result.

Examples of SELECT TOP statement:
Using TOP in constant value:

Constant value to return the top 10.

SELECT TOP 10
       First_Name,
       Salary
FROM
       EmployeeDb
ORDER BY
       First_Name,Salary DESC

Output:


 

TOP to return PERCENT of ROWS

Returns percent of total results of rows:

SELECT TOP 1 PERCENT
       First_Name,
       Salary
FROM
       EmployeeDb
ORDER BY
       First_Name DESC

Output:


 

Using TOP WITH TIES

Returns top 3 first name:

SELECT TOP 3 WITH TIES
       First_Name,
       Salary
FROM
       EmployeeDb
ORDER BY
       First_Name

Output:

Output returns four value, fourth is returned WITH TIES as it is similar to third value.

DISTINCT clause

It selects only different value which eliminates duplicate record form the result. It only operates on single column and can be used in COUNT, AVG, MAX, etc. while using DISTINCT on column with multiple NULL values, it returns only one NULL as it treats multiple NULL as one distinct value. DINSTINT cannot be operated on multiple column.

In single column:

Syntax:

SELECT DINTINCT
          <column_name>
FROM
          <table_name>

It returns distinct values from the specified column.

Examples of DISTINCT clause:

Without using DISTINCT:

SELECT
    First_Name
FROM
    EmployeeDb
ORDER BY
    First_Name

Output:

Output have duplicate first names.

Using DISTINCT:

SELECT DISTINCT
    First_Name
FROM
    EmployeeDb
ORDER BY
    First_Name

Output:

Duplicates were removed form column.

Use of DISTINCT and TOP

Implementation of DISTINCT and TOP in a column. It also cannot be operated on multiple columns.

Example:

SELECT DISTINCT TOP 10
       First_Name
FROM
       EmployeeDb
ORDER BY
       First_Name

Output:

 

  • 0
  • 0
  • 0
    • Facebook
    • Twitter
    • Google +
    • LinkedIn

About author

MAT RIX

MAT RIX

Reset Your Password
Enter your email address that you used to register. We'll send you an email with your username and a link to reset your password.

Quick Survey