Subquery returning multiple columns

Question

Hi,

I am writing a select subquery that returns a COUNT. However, I need to use a HAVING clause in the subquery and as a result need to specify that column in my subquery SELECT. So the result is that I have 2 columns: COUNT and column_for_having_clause.

Is there any way that i can tell SQL to take just the first column. I get an error saying "Operand should contain 1 column(s)". I figure this must be a common problem, does anyone know a way around it?

Also, is there a way you can execute a HAVING clause without specifying the column in your SELECT query?

Thanks!

Answer 1

You could do

SELECT blah FROM foo WHERE bar = (SELECT cnt FROM (SELECT cnt, column_for_having_clause FROM table .... HAVING ... LIMIT 1))

Quite ugly however.

Answer 2

Sure, you can specify virtually any expression you want in the HAVING clause. It doesn't have to be a column from the select-list.

SELECT ...
FROM foo
WHERE x = (SELECT COUNT(*) FROM bar 
           GROUP BY y HAVING y = MAX(y));

But you can also, if you're comparing the count to something in the outer query, compare tuples.

SELECT ...
FROM foo
WHERE (x, 1) = (SELECT COUNT(*), y = MAX(y) AS is_max FROM bar
                GROUP BY y HAVING is_max = 1);