HAVING mit MAX (alias)

[MrSpock]

Hallo Philip,

ich benötige ja das Maximum einer (alias Spalte), die wiederum eine Aggregatfunktion nutzt.

[nahpets]

Hallo,

mir ist es nur auf diese Art gelungen (allerdings kein MySQL):

Code:

select id, anz from (
  select id, count(wert) as anz
  from tabelle
  group by id
) a
where anz = (
  select max(anz) from (
    select id, count(wert) as anz
    from tabelle
    group by id
  )
)

Mit Having scheint es nicht unbedingt zu gehe(?)

[Namenloser]

Zitat von MrSpock:

Hallo Philip,

ich benötige ja das Maximum einer (alias Spalte), die wiederum eine Aggregatfunktion nutzt.

Ah sorry, hatte ich irgendwie falsch gesehn.

Ich habe folgende Varianten anzubieten:

SQL-Code:

select 
  id, 
  count(*) as anz, 
  (
    select max(anz)
    from (select count(*) as anz from Tabelle group by id) as tmp
  ) as maximum
from Tabelle 
group by id
having anz=maximum

SQL-Code:

select id, count(*) as anz 
from Tabelle
group by id
having 
  (
    select max(anz) 
    from (select count(*) as anz from Tabelle group by id) as tmp 
  ) = anz

[Bummi]

die von mir ursprünglich vorgeschlagene Lösung

Code:

SELECT id, count(*) as anz
from test
GROUP BY id
HAVING Count(*) = (Select MAX(ANZ) from (SELECT id, count(*) as anz
from test
GROUP BY id) i)

funktioniert unter MSSQL.
Funktioniert sie unter MySQL nicht?

[MrSpock]

Zitat von Bummi:

die von mir ursprünglich vorgeschlagene Lösung

Code:

SELECT id, count(*) as anz
from test
GROUP BY id
HAVING Count(*) = (Select MAX(ANZ) from (SELECT id, count(*) as anz
from test
GROUP BY id) i)

funktioniert unter MSSQL.
Funktioniert sie unter MySQL nicht?

Hallo Bummi,
die Abfrage ist Wirklichkeit deutlich komplexer, deshalb war es mit dem Testen nicht so einfach. Ich habe es aber nochmal ausprobiert und behaupte, dass es nicht funktioniert!
Es wird mir kein Datensatz ausgegeben. Ich vermute, dass die Gleichung in der Having Clause nicht funktioniert.

Zitat von Furtbichler:

Zitat von MrSpock:

warum funktioniert folgende query nicht?

Weil die Syntax falsch ist: Du wendest in der HAVING-Klausel ein Aggregat auf eine Spalte der Ergebnistabelle an. Erlaubt sind jedoch nur Aggregate auf Spalten der Ursprungstabelle.

Zitat:

Interessanterweise werden alle Werte gezeigt, wenn ich schreibe

Code:

HAVING (Max(anz) = 0)

Ich finde das nicht interessant, sondern typisch mySQL.

Der Weg führt imho nur über eine 'temporäre Tabelle', d.h. die Aggregat-Tabelle mit den Spalten id und Anz (also Count(*)). Davon möchstest Du die Einträge, deren Wert Anz am größten ist.

Bei meiner Arbeit mit MSSQL ist mir aufgefallen, das die Verwendung temporärer Tabellen häufiger wesentlich performanter ist, leichter verständlich und schneller zum Ziel führt:

SQL-Code:

select Id, Count(*) as Anz into #temp from myTable group by Id
select * from #temp where Anz = (select max(Anz) from #temp)

Kennt mySQL temporäre Tabellen oder Tabellenvariablen? ....*gugel* ja, geht

SQL-Code:

create Temporary table Anzahl (id int, Anz int)
insert into Anzahl select id, count(*) from myTable
select * from Anzahl where Anz = (select max(Anz) from Anzahl)
DROP TABLE Anzahl

Hier scheint es so zu sein, das eine Lösung mit einem Statement nur über den Klimmzug geht, die Aggregattabelle 'Select id, count(*)...' mehrfach zu verwenden. Schafft der Compiler es, dies zu erkennen und das Aggregat nur 1x durchzuführen?

OK, diese Lösung scheint mir wirklich die beste zu sein. Ein Problem hat MySQL aber noch mit dem

select * from #temp where Anz = (select max(Anz) from #temp) in einer Abfrage kann eine temporary table offensichtlich nicht zweimal benutzt werden. Deshalb musste ich noch eine zweite temp table benutzen

[Furtbichler]

Hi Spock

Ich habe 0 Ahnung von mysql und deshalb sind meine Vorschläge nur Theorie.
Wenn mySQL mit Variablen umgehen kann, könntest Du dir die höchste Anzahl in einer Variablen merken und dann auf die 2.Tabelle verzichten.
So z.B.

SQL-Code:

DECLARE maxAnzahl INT
create Temporary table Anzahl (id int, Anz int)
insert into Anzahl select id, count(*) from myTable
select max(Anz) from Anzahl into maxAnzahl
select * from Anzahl where Anz = @maxAnzahl
DROP TABLE Anzahl

Wäre denkbar, das das nur in einer Prozedur oder einem BEGIN-END Block geht.

[Namenloser]

Die Lösung von Bummi und mir macht letztlich ja auch nichts anderes, nur dass die temporäre Tabelle nicht explizit angelegt wird.

SQL-Code:

select max(anz) from (
  select count(*) as anz from Tabelle group by id /* ← temporäre Tabelle */
)

[Furtbichler]

Na doch. Mr.Spock möchte alle Einträge, deren Spalte 'ID' am häufigsten vorkommt.
Deine Query liefert nur die maximale Anzahl.

Der kleine Frickelansatz von mir liefert zumindest die Spalten 'ID'