how this code works

[satishpkumarin]

hi
i have some thing like this code,
use pubs

select (select count(au_id) from pubs..author i where i.au_id >=o.au_id) as row id ,au_fname+au_lname as authorname from author a order by row id

it results in

rowid authorname
1 anneyasfk
2 adfkasdf
3 dafasdfs
--------
now i have doubt,
when i execute the inner query
select count(au_id) from pubs..author i where i.au_id >=o.au_id

the results is
-------
276
------

but if i execute the whole query
i see the result as the first result

can any one explains how does this runs

[andi_g69]

Sorry to say, but I have doubts you are testing properly. The inner query cannot be execued stand-alone because it references the outer query.

If you run this code:
select (
select count(au_id)
from pubs..authors i
where i.au_id >=o.au_id) as [row id]
, au_fname+au_lname as authorname
from authors o
order by [row id]

it results in 23 records

The query
select count(au_id)
from pubs..authors i

gives a count of 23 as expected.

Executed on a SQL2K SP3 Standard edition without any modification to the pubs database