select in the select

[luciffer]

its friday, thats all i have to say!!

how would you do this

select b.id, rownum rn, (select count(id) FROM b) id_cnt
from (SELECT * FROM caw_dataobjects WHERE id < 1000) b;

This is obviously wrong, so is it possible to do the 'from' on the first line by referencing that table labelled as 'b' on the second line?
I'm trying to do it without repeating the query like this

select b.id, rownum rn, (select count(id) FROM (SELECT * FROM caw_dataobjects WHERE id < 1000)) id_cnt
from (SELECT * FROM caw_dataobjects WHERE id < 1000) b;

[slimdave]

The most efficient way would be to use an analytic function, something like ...

select
   col1,
   col2,
   ...
   total_rows
From
   (
   select
      col1,
      col2,
      ...
      Count(*) Over () total_rows
   from
      caw_dataobjects
   where id < 1000
   )
/

[luciffer]

That'll do nicely, thanks.

I never knew about the analytic functions.