SQL SERVER TOP CLAUSE

SQL SERVER TOP CLAUSE   returns rows by number, percentage.

for ex:  select top 3 highest paid employees in emp table

select  top 3 * from emp;

EMPNO    ENAME    JOB    MGR    HIREDATE    SAL    COMM    DEPTNO
7839    KING    PRESIDENT    NULL    1981-11-17    5000.00    NULL    10
7902    FORD    ANALYST    7566    1981-12-03    3000.00    NULL    20
7566    JONES    MANAGER    7839    1981-04-02    2975.00    300.00    20


EMP TABLE

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	12/17/1980 12:00:00 AM	800.00		20
7499	ALLEN	SALESMAN	7698	2/20/1981 12:00:00 AM	1600.00	300.00	30
7521	WARD	SALESMAN	7698	2/22/1981 12:00:00 AM	1250.00	500.00	30
7566	JONES	MANAGER	7839	4/2/1981 12:00:00 AM	2975.00		20
7654	MARTIN	SALESMAN	7698	9/28/1981 12:00:00 AM	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	5/1/1981 12:00:00 AM	2850.00		30
7782	CLARK	MANAGER	7839	6/9/1981 12:00:00 AM	2450.00		10
7839	KING	PRESIDENT		11/17/1981 12:00:00 AM	5000.00		10
7844	TURNER	SALESMAN	7698	9/8/1981 12:00:00 AM	1500.00	0.00	30
7900	JAMES	CLERK	7698	12/3/1981 12:00:00 AM	950.00		30
7902	FORD	ANALYST	7566	12/3/1981 12:00:00 AM	3000.00		20
7934	MILLER	CLERK	7782	1/23/1982 12:00:00 AM	1300.00		10

Ex:  Get top 5 Maximum sold products (from northwind)

select top 5 p.productid,od.productid,ProductName,od.[Total Price] from products p
join
(
select productid,Quantity*unitprice [Total Price] from [order details]
) od
on p.productid=od.productid
order by [Total Price] desc

O/P:
productid    productid    ProductName    Total Price
38    38    Côte de Blaye    15810.00
38    38    Côte de Blaye    15810.00
38    38    Côte de Blaye    10540.00
38    38    Côte de Blaye    10540.00
38    38    Côte de Blaye    10540.00


Ex:get top 3 shipped countries

select top 3 shipcountry,count(*) shipped from orders group by shipcountry
order by shipped desc
                             OR
select top 3  *
from (select shipcountry,count(*) shipped from orders group by shipcountry) o
order by shipped desc

shipcountry    shipped
USA    122
Germany    122
Brazil    83


Ex: get 5 percentage of orders placed in year 1996(northwind DB)

select top 5 percent  * from orders
where DATEPART(year,ShippedDate)=1996

User will get 8 rows, out of 143 rows   5%of 143 = 7.15 it will be ceiled ,so you will get 8 records

Tags:SQL SERVER TOP CLAUSE,using TOP CLUASE in T-SQL, using TOP number,using TOP percent in SQL-SERVER