Clever Geek Handbook

Subquery returning multiple columns

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

Is there a way I can tell SQL to take only the first column. I am getting the error "The operand must contain 1 column". I suppose this must be a common problem, does anyone know how to do this?

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

Thank!

+2


source to share


2 answers


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.

+1


source


Of course, you can specify almost any expression you want in the sentence HAVING

. It doesn't have to be a selectable column.

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


But you can also, if you are comparing a counter to something in an 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);
+1


source






More articles:


All Articles