[SQL] SELECT max

[Jurgle]

Er zijn twee tabellen, zij hebben een gedeelde id. M.a.w: De id van tabel A komt niet voor in tabel B en andersom. Verder zijn deze id's uniek in de tabel zelf.

Als ik een nieuwe row aan een van de tabellen wil toevoegen, moet ik dus eerst kijken welke eerst volgende ID vrij is:
SELECT max(a_id) ...
en
SELECT max(b_id) ...

De nieuwe id is dan:
new_id = max(a_id, b_id) + 1;

Dit werkt, maar is wat omslachtig. Dit moet in 1 query kunnen.

Mijn vraag is daarom: Hoe formuleer ik een query die als uitkomst de nieuwe id heeft?

[curry684]

Kan dit niet gewoon met een join?

* curry684 is SQL noob...

[Verwijderd]

select max(id) from tabelA, tabelB

edit:
sorry, zo iets werd hierboven al gezegd....

[ Voor 47% gewijzigd door Verwijderd op 30-07-2003 16:33 ]

[Varienaja]

Net eventjes getest:

selext max(nummer) from
(
select nummer from tabel1 union
select nummer from tabel2
)

Is niet echt snel trouwens...

Overigens:

Verwijderd schreef op 30 July 2003 @ 16:31:
select max(id) from tabelA, tabelB

ORA-00918: column ambiguously defined

[ Voor 55% gewijzigd door Varienaja op 30-07-2003 16:33 ]

[Jurgle]

Ik heb nu dit:

SELECT MAX(A.a_id) AS a_id, MAX(B.b_id) AS b_id FROM tabela A, tabelb B

vervolgens ga ik in PHP met een if-boom kijken wat ik moet returnen.

De twee opties hierboven: De id's hebben verschillende namen. (Dit moet doordat dit zo gebruikt wordt in het al bestaande systeem). Als ik deze in een MAX om kan zetten in dezelfde naam is het probleem idd ook opgelost.

De snelheid is juist de rede dat ik het in 1 query wil doen en het lieftst zonder IF constructie. Als de 'nieuwe id' zelfs in de INSERT query berekend kan worden is dat natuurlijk helemaal mooi.

[ Voor 22% gewijzigd door Jurgle op 30-07-2003 16:36 ]

[Standeman]

Als je een Oracle DB hebt, kan je beter gebruik maken van sequences. (Ik weet niet of overige DB's ook zoiets hebben)

[RobIII]

offtopic:
Dit riekt vreselijk naar een slecht DB ontwerp

Kun je geen stored procedure gebruiken en daar dan die select in gooien met die if-constuctie? Dan kun je die mooi aanroepen.

[Verwijderd]

Deze join zou een stukje sneller moeten zijn

SQL:

selext max(num) from
(
select max(nummer) as num from tabel1 union
select max(nummer) as num from tabel2
)

[Jurgle]

RE: offtopic:
Is idd niet zo mooi, maar kan er niet omheen, al bestaand systeem.

Stored procedure -> ga ik uitzoeken, ken ik nog niet.

-------------------------------------------

union wordt pas ondersteund v.a. MySQL 4.1.0 daar draait dit zooitje niet op...

[ Voor 34% gewijzigd door Jurgle op 30-07-2003 16:56 ]

[RobIII]

Jurgle schreef op 30 July 2003 @ 16:55:
RE: offtopic:
Is idd niet zo mooi, maar kan er niet omheen, al bestaand systeem.

Stored procedure -> ga ik uitzoeken, ken ik nog niet.

-------------------------------------------

union wordt pas ondersteund v.a. MySQL 4.1.0 daar draait dit zooitje niet op...

huh? Waar draai je dan wel op? Voor zover ik weet ondersteunen alleen MS SQL en Oracle SQL een "stored procedure" of iets wat erop lijkt MySQL kan daar volgens mij niet mee overweg? Maar ik kan me vergissen hoor...

[bigtree]

mysql:

code:

SELECT IF(
  MAX(A.a_id) > MAX(B.b_id), 
  MAX(A.a_id), 
  MAX(B.b_id)) AS hoogste_id 
FROM tabela A, tabelb B

[Annie]

Jurgle schreef op 30 July 2003 @ 16:34:
De snelheid is juist de rede dat ik het in 1 query wil doen en het lieftst zonder IF constructie. Als de 'nieuwe id' zelfs in de INSERT query berekend kan worden is dat natuurlijk helemaal mooi.

Als snelheid een issue is dan lijkt me een full join niet heel erg slim. Ik weet zo niet of en hoe de database dit optimaliseert als de max wordt opgevraagd, maar bij veel records kan ik me voorstellen dat het sneller is om 2 queries uit te voeren.

Over welke database hebben we het eigenlijk?

edit:
ah, mysql dus

[ Voor 4% gewijzigd door Annie op 30-07-2003 17:01 ]

[justmental]

code:

SELECT GREATEST(MAX(A.a_id), MAX(B.b_id)) AS max_id FROM tabela A, tabelb B

[bigtree]

justmental schreef op 30 juli 2003 @ 19:29:

code:

SELECT GREATEST(MAX(A.a_id), MAX(B.b_id)) AS max_id 
FROM tabela A, tabelb B

Wow, die is nog beter. Het blijkt dat mijn oplossing niet NULL-safe is en GREATEST wel.

edit:
Heyyyy, dit blijkt niet te kloppen. Als MAX(B.b_id) IS NULL dan geeft GREATEST() als resultaat NULL, maar als MAX(A.a_id) IS NULL dan geeft GREATEST() als resultaat MAX(B.b_id).
En da's nie consistent, want:

GREATEST(3, NULL) = 3
en
GREATEST(NULL, 3) = 3


Ziet iemand hier de logica van in of mag deze op ACM's beruchte lijstje.

[ Voor 45% gewijzigd door bigtree op 30-07-2003 19:58 ]

[Grijze Vos]

Ik moet zeggen dat ik het ernstig ranzig vind om tabellen ID's te laten sharen. Het hele idee achter een ID, is dat ie per tabel uniek is.

Hoe heb je in hemelsnaam je database genormaliseerd, daar zet ik echt ernstige vraagtekens bij.

Mja, je moet et zelf weten. Je antwoord heb je al gekregen...

[pgussow]

Nou weet ik niet helemaal zeker of wat ik ga beweren juist is of niet. Heb het niet getest.
Je hebt geen join zitten tussen tabel a en b. Volgens mij is het dan zo dat die MAX(id) gedaan wordt over de resultset van de query select a_id, b_ib from tabela, tabelb. Stel dat tabelA de volgende rijen bevat: 1, 2 en TabelB 3, 4. Dan wordt de resultset van SQL:

A_ID B_ID
1 3
1 4
2 3
2 4

Dus ipv de max-bepaling over 2 resultrijen gaat het over 4 rijen. Dus extra vertragend.. Correct me if I'm wrong

[whoami]

Laat de databank zorgen voor een nieuw id.

In Oracle heb je daarvoor sequences, in MS SQL Server heb je daarvoor identity columns.
Ga zelf niet zitten klooien met een select max() om een nieuw id te creeëren, dit kan alleen maar zorgen voor concurrency-problemen.

[Goodielover]

Waarom zou je dit in 1 query willen doen?
de snelheid om het gewoon met twee queries te doen is veel beter.
Er is geen relatie tussen de tabellen, dus probeer die ook niet te leggen.

Twee aparte max-en dus en zelf even de greatest nemen.

Daarnaast ben ik helemaal met Grijze Vos (hierboven) eens. de logica om over meer dan 1 tabel een unieke ID te leggen ontgaat me. Tenzij het ontwerp niet zuiver is gehouden.....

[bigtree]

Goodielover schreef op 31 juli 2003 @ 11:50:
Waarom zou je dit in 1 query willen doen?
de snelheid om het gewoon met twee queries te doen is veel beter.
Er is geen relatie tussen de tabellen, dus probeer die ook niet te leggen.

Twee aparte max-en dus en zelf even de greatest nemen.

Als je uit twee tabellen de hoogste waarde van de primaire sleutel wilt hebben, is dat sneller met één query dan met twee. MySQL legt namelijk geen relatie tussen de tabellen als je geen JOIN gebruikt en alleen maar MAX() selecteert.

[servies]

Ok, dit is dus een bestaand systeem, waarbij de tabellen niet samengevoegd kunnen worden...
Als het puur om de snelheid gaat is er in dit geval eigenlijk maar 1 oplossing:
1 extra tabel waarin het hoogste gebruikte nummer wordt bijgehouden.
Als je dan een nieuw record toevoegd aan 1 van de 2 tabellen lees je dat nummer uit die tabel en update je 'm daar ook. Je hebt dan geen select max(id) op beide tabellen meer nodig alleen maar bijvoorbeeld een "SELECT maxid FROM hoogstenummers WHERE tabelnaam LIKE 'gezamenlijketabel'
En bij de insert van het nieuwe record hoog je die maxid ook nog eens op voor de desbetreffende tabel.
Het enige waar je voor moet zorgen is dat dit dan ook bij iedere insert in een van die tabellen gebeurt.

[whoami]

bigtree schreef op 31 July 2003 @ 12:15:
[...]
Als je uit twee tabellen de hoogste waarde van de primaire sleutel wilt hebben, is dat sneller met één query dan met twee. MySQL legt namelijk geen relatie tussen de tabellen als je geen JOIN gebruikt en alleen maar MAX() selecteert.

Erm, wrong.

Als je doet:

code:

select max(tabel1.id), max(tabel2.id)
from tabel1, tabel2

Dan zal MySQL (en ieder ander DBMS) een FULL join doen van tabel1 en tabel2.
Je krijgt dan dus een cartesiaans product: voor ieder record van tabel1 wordt ieder record van tabel2 teruggegeven.
Deze query gaat dus zeker langzamer dan 2x een query (1 op tabel1 en 1 op tabel2).
Feel free om het even uit te testen.

Daarnaast: aangezien er geen relatie bestaat tussen tabel1 en tabel2 kan je ook niet joinen.
Wat je wel kunt doen , is een UNION gebruiken (als mySQL dat al ondersteunt).

code:

select max(id)
from tabel1
union
select max(id)
from tabel2

Je krijgt dan 2 records terug, met respectievelijk de hoogste waarde uit tabel1 en tabel2.

Daarnaast blijft de vraag of je dat echt zo wil doen; je id's gaan genereren.

[Annie]

De union was al ter sprake gekomen. Alleen kan de TS daar niets mee (versie ondersteund het nog niet).

Verder voor iedereen die nu ineens de performance van de full join belangrijk vindt: [rml]Annie in "[ SQL] SELECT max"[/rml]

* Annie voelt zich genegeerd

[whoami]

Annie schreef op 31 July 2003 @ 12:38:
Verder voor iedereen die nu ineens de performance van de full join belangrijk vindt: [rml]Annie in "[ SQL] SELECT max"[/rml]

Ik geef je gelijk wb snelheid en FULL JOIN.
Zie m'n post hierboven.

* Annie voelt zich genegeerd

[/quote]

* whoami pets Annie.

[bigtree]

Ik heb twee punten om mijn stelling te bewijzen

Ten eerste: output van

code:

EXPLAIN SELECT GREATEST(MAX(A.a_id), MAX(B.b_id)) AS max_id 
FROM tabela A, tabelb B

levert:

Select tables optimized away

Oftewel; pak de MAX() waarde uit de primaire sleutel (ik ga er van uit dat a_id en b_id beide een primaire sleutel zijn in hun resp. tabellen). De tables worden dus in zijn geheel niet gescand.

Ten tweede: stukje uit de manual (How MySQL Uses Indexes):

Find the MAX() or MIN() value for a specific indexed column. This is optimised by a preprocessor that checks if you are using WHERE key_part_# = constant on all key parts < N. In this case MySQL will do a single key lookup and replace the MIN() expression with a constant. If all expressions are replaced with constants, the query will return at once:

'nuf said?

[whoami]

Test het gewoon eens uit.
Ik heb hier geen MySQL draaien, maar wel SQL Server. In SQL Server (en ook in Oracle) levert een SELECT met 2 of meer tabellen in de FROM clause zonder dat ze gejoined worden een cartesiaans product.

[bigtree]

Normaal gesproken: uiteraard! Maar in dit bijzondere geval selecteer je twee constanten. Vergelijk het met:

code:

SELECT 1, 2

Hier selecteer je twee constanten in één rij.

Maar als je

code:

SELECT 1, 2 FROM tabela

uitvoert, krijg je een rij met 1, 2 voor elk record dat er in de tabel zit. Wel optimaliseert mysql dit zo dat hij de primaire sleutel gebruikt om te bepalen hoe veel records met 1, 2 moet retourneren. Dat kan je zien aan:

code:

EXPLAIN SELECT 1, 2 FROM tabela

Als je echter een

code:

EXPLAIN SELECT MAX(a_id) FROM tabela

uitvoert, zul je zien dat hij die hele tabel met rust laat, alleen maar even kijkt wat de hoogste a_id is (op basis van de hoogste waarde van de primaire sleutel). Vandaar dat hij bij uitvoeren van

code:

SELECT MAX(a_id) FROM tabela

maar één record teruggeeft in plaats van een rij voor elk record in de tabel.

Aangezien je in

code:

SELECT MAX(A.a_id), MAX(B.b_id) FROM tabela A, tabelb B

eigenlijk gewoon twee constanten selecteert (mysql optimaliseert dit!) retourneert hij ook maar één rij. In dit geval is er dus ook geen sprake van een cartesiaans product.

[ Voor 1% gewijzigd door bigtree op 31-07-2003 14:24 . Reden: select verbeterd ]

[whoami]

Hmmm.... Idd.
Ik heb het net ff uitgetest op SQL Server en daar werkt het ook zonder cartesiaans product als ik select max(tabel1.id), max(tabel2.id) from tabel1, tabel2 doe.

[JaQ]

Je zit dus nog met null values. Die kan je toch afvangen (met nvl in Oracle, isnull in postgresql en ongetwijfeld ook een of andere functie in mysql, maar die zoek je zelf maar ff op).

Stored procedures kunnen trouwens ook in firebird, postgresql, sapdb en ongetwijfeld nog veel meer rdbms-en.

[Goodielover]

null values in een PK-veld??? lijkt me niet, hoop ik

[bigtree]

Goodielover schreef op 31 July 2003 @ 16:40:
null values in een PK-veld??? lijkt me niet, hoop ik

Niet in de tabel, maar

code:

SELECT MAX(id) FROM tabel

op een lege tabel geeft NULL, vandaar.