incoporating MAX in sql

[Qwerty8989]

I am wanting to display the heighest tree giving the name of the tree, its height, which supplier supplies it including the suppliers name and address. I know I need a MAX in there somewhere, however, this shows what I want:


SELECT PLANTEN.PLANTNAAM, PLANTEN.SOORT, PLANTEN.HOOGTE, LEVERANC.LEV_NAAM, LEVERANC.ADRES
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO
WHERE (PLANTEN.SOORT='boom' AND PLANTEN.HOOGTE=4000);

The last bit PLANTEN.HOOGTE=4000 (this being the height of the tree) isn't what I need since it will only show that tree even if higher trees are added. Therefore I know I need a SELECT MAX(PLANTEN.HOOGTE) FROM PLANTEN somewhere, but when I try to do it I get the wrong results. Any suggestions would be appreciated =)

 

[FoFa]

Where you have AND PLANTEN.HOOGTE=4000 you can substitute a subquery pulling the max for that soecific one.
It would be something like;
AND PLANTEN.HOOGTE=(SELECT MAX(A.HOOGTE) from PLANTEN A Where A.SOORT = PLANTEN.SOORT)
Now this assumes that SOORT makes the HOOGTE unique for whatever it is you are doing

 

[Qwerty8989]

This doesn't seem to work, I tried a few variations but it won't work :s

I also tried this:

SELECT PLANTEN.PLANTNAAM, PLANTEN.SOORT, Max(PLANTEN.HOOGTE) AS Max_HOOGTE, LEVERANC.LEV_NAAM, LEVERANC.ADRES
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO
GROUP BY PLANTEN.PLANTNAAM, PLANTEN.SOORT, LEVERANC.LEV_NAAM, LEVERANC.ADRES
HAVING (((PLANTEN.SOORT)='boom'));


but now it brings up all the results =(

 

[FoFa]

Try this just to start:
SELECT PLANTEN.PLANTNAAM, PLANTEN.SOORT, Max(PLANTEN.HOOGTE) AS Max_HOOGTE, LEVERANC.LEV_NAAM, LEVERANC.ADRES
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO
WHERE (((PLANTEN.SOORT)='boom'))
GROUP BY PLANTEN.PLANTNAAM, PLANTEN.SOORT, LEVERANC.LEV_NAAM, LEVERANC.ADRES;