Posted by David Portas on 09/30/66 12:00

"Gints Plivna" <gints.plivna@gmail.com> wrote in message
news:cb75a2fd-9514-4505-8c51-35ad9a95fba1@p69g2000hsa.googlegroups.com...
> I'm coming from Oracle world and trying to find something similar to
> rownum in Oracle. I know there exists TOP which normally if used in
> the same select woth order by firstly sorts data and then only gets
> top n. So the question is what actually happens when top is used in
> inner query and order by in outer query. The problem is that it seems
> to be somehow inconsistent at least for the first sight.
>
> Using SQL Server 2005
> So I have following test case:
>
> create table t3 (id integer, data varchar(4000));
> insert into t3 values (1, replicate('a', 4000));
> insert into t3 values (2, replicate('b', 4000));
> insert into t3 values (3, replicate('c', 4000));
> insert into t3 values (4, replicate('d', 4000));
> insert into t3 values (5, replicate('e', 4000));
> insert into t3 values (6, replicate('f', 4000));
> insert into t3 values (7, replicate('g', 4000));
> insert into t3 values (8, replicate('h', 4000));
> insert into t3 values (9, replicate('i', 4000));
>
> SET STATISTICS IO ON
> firstly just select all rows to know how many logical reads are needed
> for all table.
>
> select * from t3
> 1 aaa...
> ...
> 9 iiii....
> logical reads 5
>
> Now get first two rows without any where clause:
> select top 2 * from t3
> 1 aaa...
> 2 bbb...
> logical reads 1
>
> Now the same first two rows just with outer select without any order
> by:
> select * from (
> select top 2 * from t3
> ) as q
> 1 aaa...
> 2 bbb...
> logical reads 1
>
> OK till now it's as expected, just one logical read get first 2 rows
> and end query.
> However look at next query's logical reads 5. This somehow is very
> interestingly equal to logical reads for select all rows from t3.
>
> select * from (
> select top 2 * from t3
> ) as q
> order by data asc
> 1 aaa...
> 2 bbb...
> logical reads 5
>
> So the next one shows that order by clause has affected the result set
> and actually semms to be pushed into inner query. Also logical reads
> are 5 meaning that actually we have scanned all the table.
>
> select * from (
> select top 2 * from t3
> ) as q
> order by data desc
> 9 iii..
> 8 hhh...
> logical reads 5
>
> However for TOP 1 everything works in a different way i.e. there is
> always the same one row and the same one logical read in spite of
> diffferent order by clauses:
>
> select * from (
> select top 1 * from t3
> ) as q
> order by data asc
> 1 aaa....
> logical reads 1
>
> select * from (
> select top 1 * from t3
> ) as q
> order by data desc
> 1 aaa....
> logical reads 1
>
> So where is the truth? Why the functionality is different?
>
> The business case is that we have search with potentially weak user
> criteria resulting in BIG potential result sets, but we want to show
> the user just ANY N rows satisfying criteria. But these N rows should
> be ordered. So what I'd like to achieve is:
> 1) get ANY no more than N rows according to my criteria
> 2) sort these N rows according to my order by clause.
>
> I DEFINITELY don't want:
> 1) get ALL rows
> 2) sort them and throw away all but first N.
>
> TIA, Gints

TOP n without ORDER BY is non-deterministic. Ie. you are telling SQL Server
to return ANY n rows from the table, which it will therefore do by whatever
method it finds convenient. Since a table is an unordered set of rows by
definition the only way to select a specific "top n" rows is to specify some
logical ordering. This is much the same as with rownum in Oracle, which is
not bound to any fixed ordering in the table.

See also the ROW_NUMBER() function, which is standard SQL and supported by
both Oracle and SQL Server.

Hope that helps.

--
David Portas

[Back to original message]