Top N Query - SQL Server 2008

How would i return Top N Queries from SQL Server. I know how it 开发者_C百科is done in Oracle

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal FROM (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal FROM Emp ORDER BY NVL(Sal, 0) DESC) WHERE ROWNUM < 6;

But how is the same query written in SQL Server ?. I never worked on SQL Server. So any classic solution is appreciated.

Thanks

In SQL server you achieve this behavior like so:

SELECT TOP 6 Empno, Ename, Job, Mgr, Hiredate, Sal FROM Emp ORDER BY NULLIF(Sal, 0) DESC;

It's easy:

SELECT TOP 100 ....

You could try

select top <n> ....

I think what you are looking for is select top N, like this:

SELECT TOP 6 Empno, Ename, Job, Mgr, Hiredate, Sal FROM (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal FROM Emp ORDER BY NVL(Sal, 0) DESC);

Just use TOP in the select to get the first values acording to the order clause, or if it doesn´t exisits, acording to the key or indexes.

SELECT  top <n> *
FROM    Table1
ORDER BY OrderCol <desc>