reuse table alias in another select

I have a sql statement:

select id from table1 t1, table t2 开发者_StackOverflow中文版
where.....
order by ( select count(owner_id) from t2) ASC;

What I want to do here is to select the id of the item whose owner has least number of items.

Is this possible? If not, what I can do to achieve to goal?

Thanks in advance!

You don't mention what SQL you're using but you can do this, or something similar, in PL ( and My I believe ); I'm assuming you're linking table 1 and 2 on id; I haven't ordered by the count(owner_id) alone as this will always be the same value. Obviously partition by whatever you want to get the correct count you're after.

select id
  from ( select t1.id, t2.ct
           from table1 t1
              , ( select id, count(owner_id) over ( partition by id ) as ct
                    from table2 )  t2 
          where t1.id = t2.id 
          order by t2.ct ASC )
       ;