How to get only rows that are the max of a column with a subquery, using one query?

Question

Very similarly to this question, I'd like to only have rows that are the max of a column. All of these solutions seem to work great when working with a table, but I'm trying to do this with a subquery. I'd like to get this down to one query.

The two methods I've thought of to accomplish this are: a) using temporary tables, or b) duplicate the subquery code. The temporary tables method is straightforward, but forces me to use multiple queries. The duplicate code works as well, but, well... it's duplicate code.

What I want to do is something along the lines of an INTO within a subquery, so I can re-use the info from that subquery:

select ...
from (
    select ..., count(someColumn) as countColumn
    into #tempTable
    where ...
    group by ...
)
where countColumn = (select max(countColumn) from #tempTable)

but apparently that's not allowed...

Is this possible to do in one query, without duplicating my subquery?

score 1 · Accepted Answer · answered Oct 30 '14 at 03:08

How about using a CTE?

with t as (
      select . . ., count(someColumn) as countColumn
      where . . .
      group by . . . .
     )
select *
from t
where countColumn = (select max(CountColumn from t);

You can also do this with analytic functions:

select *
from (select . . ., count(someColumn) as countColumn,
             max(count(someColumn)) over () as maxcountColumn
      where . . .
      group by . . .
     ) t
where countColumn = maxcountColumn;

How to get only rows that are the max of a column with a subquery, using one query?

1 Answers1