|
|
Posted by Erland Sommarskog on 01/23/08 23:17
Gints Plivna (gints.plivna@gmail.com) writes:
> I'm quite sure row_number won't help me in this case, because I'd like
> to limit found number of rows. row_number actually must have order by
> clause and ordering before limiting returned number of rows is the
> thing I'd like to avoid.
That can be achieved with row_number with some trickery. Consider:
with numbered AS (
select *, rn = row_number() OVER (order by x)
from (SELECT *, x = 'x' FROM Orders) as s
)
SELECT * FROM numbered WHERE rn < 20
ORDER BY CustomerID
> And also this code will run only on SQL Server, so no need for
> "portable SQL" (I'm BTW quite sceptical about such "portable SQLs"
> generally, because usually it means code will be slow on all
> databases :).
Sorry, since you mentioned that you came from Oracle, I somehow drew
the conclusion that you were porting code.
I agree on your opinion on "portable SQL".
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|