How to use nested select queries in Drupal 7

Question

How can I write the following query in a Drupal-friendly way?

select max(cnt) from 
(select COUNT(*) as cnt from Det a,
dbo.Mem b where a.no=b.no
group by b.no) X
go

Answer 1

Since you're going cross-database I think using db_select() could get quite complicated. I might be wrong though, it might just work.

Either way, you could always just run the query directly with db_query():

$sql = '
  select max(cnt) from 
  (select COUNT(*) as cnt from Det a,
  dbo.Mem b where a.no=b.no
  group by b.no)';

$count = db_query($sql)->fetchField();