CODEWITHSUNDEEP
sqloracle-databaseselectsubquery
Petra1999
Petra1999

Reputation: 65

Two SELECT-Statement work alone but not together

I have two select statements that work on their own, but not together.

This:

SELECT MAX(a2.Fachanzahl)
FROM C17_AbfrageBView a2;

works and returns one row with one column with the value 2

This:

SELECT a1.PersonID, a1.Vorname, a1.Nachname, MAX(a1.Fachanzahl) Fachanzahl
FROM C17_AbfrageBView a1
GROUP BY a1.PersonID, a1.Vorname, a1.Nachname
HAVING MAX(a1.Fachanzahl) = 2;

works and returns the correct row.

However, this:

SELECT a1.PersonID, a1.Vorname, a1.Nachname, MAX(a1.Fachanzahl) Fachanzahl
FROM C17_AbfrageBView a1
GROUP BY a1.PersonID, a1.Vorname, a1.Nachname
HAVING MAX(a1.Fachanzahl) = (
                              SELECT MAX(a2.Fachanzahl)
                              FROM C17_AbfrageBView a2
                            );

does not return anything (it should return the same row as the above statement), even though the outer and inner select statements work on their own. What is the problem here?

Thank you!

Upvotes: 4

Views: 175

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I cannot think of a mechanism where this would happen. As far as I know, MAX() does not change the type of a column, ruling out things like float rounding errors or collation incompatibilities.

I can say that the query would be more efficient if written using WHERE rather than HAVING:

SELECT DISTINCT a1.PersonID, a1.Vorname, a1.Nachname, a1.Fachanzahl
FROM C17_AbfrageBView a1
WHERE a1.Fachanzahl = (SELECT MAX(a2.Fachanzahl)
                       FROM C17_AbfrageBView a2
                      );

If the MAX() is introducing some problem with nano-changes to the value, then this might fix the problem.

Because you only care about the maximum value (at least in the query in the question), you can filter first. This makes the query much more efficient. The difference between SELECT DISTINCT and GROUP BY should be between nothing and negligible. The former is easier to type.

Upvotes: 1

Related Questions