top in sql | top command in sql

Top Command

The top command is used to select the top value from the table. We have to give the number of row with the top command.

Example of Top Command

Consider the following table as example in which it shows the prod_year, dept and no_of_prod.

		


		Figure 1

Suppose in this table if you want to select top 2 rows then you will use the top command as follows:-

		
select top 2 * from prod_rep;

The output of this command as Follows:-

		


		Figure 2

This is the use of top command.

Let’s solve some queries using top command.

For example if you want to select top highest production and lowest production then you will use top command with order by statement.

If you want to find highest number of production then you will run query as follows:-

		
select top 1 * from prod_rep order by No_of_Prod desc

The output of this query will be as follows:-

		


		Figure 3

Similarly if you want to find the lowest number of production then you will run the query as follows:-

		
select top 1 * from prod_rep order by No_of_Prod

The output of this query will be as follows:-

		


		Figure 3

Hope you understand the concept of top. Now let’s move to some other queries using top command.

second highest salary in sql

In these query we find the highest and lowest number of production. Suppose if you want to select second highest or 3rd highest production or 2nd lowest and 3rd lowest production etc. then we will run the query as follows:-

To find the 2nd highest salary:-

		
select top 1 * from (select top 2 * from prod_rep order by No_of_Prod desc) as Temp order by No_of_Prod

The output of this query as follows:-

		


		Figure 5

Use of percent in Top command

we can also select Data in percentage (%) using top command. Using percentage it will show the row accordingly. For example if table contains 4 rows and I select 50% then it will Show 2 rows.

		
select top 50percent *  from prod_rep;