SuperMan
Reputation: 3584
Top N Query - SQL Server 2008
How would i return Top N Queries from SQL Server. I know how it 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
Upvotes: 2
Views: 2395
Answers (5)
FreeAsInBeer
Reputation: 12979
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;
Upvotes: 3
pcofre
Reputation: 4066
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>
Upvotes: 1
ChrisWue
Reputation: 19020
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);
Upvotes: 1
