SQL - Aggregate sql query

Question

I have to table named CollecteEncombrants_Terrain and CollecteEncombrants_Stops with the following structure.

The name column in CollecteEncombrants_Stops contains the globalid from CollecteEncombrants_Terrain.

I would like to select the entity in CollecteEncombrants_Terrain with the maximum sequence from CollecteEncombrants_Stops from all the entities where column collecteFaite is not null.

I tried the following query but it returns empty selection:

SELECT * 
FROM CollecteEncombrants_Terrain 
WHERE globalid = (SELECT name 
                  FROM CollecteEncombrants_Stops 
                  WHERE sequence = (SELECT MAX(sequence) 
                                    FROM CollecteEncombrants_Stops 
                                    WHERE name IN (SELECT globalid 
                                                   FROM CollecteEncombrants_Terrain 
                                                   WHERE collecteFaite IS NOT NULL)))

Answer 1

I would like to select the entity in CollecteEncombrants_Terrain with the maximum sequence from CollecteEncombrants_Stops from all the entities where column collecteFaite is not null.

Start with:

SELECT * 
FROM CollecteEncombrants_Terrain 
WHERE collecteFaite is not null

add the link to CollecteEncombrants_Stops:

SELECT * 
FROM CollecteEncombrants_Terrain ct
INNER JOIN CollecteEncombrants_Stops cs on cs.name = ct.globalid
WHERE collecteFaite is not null

After that add the appropriate filter by adding stuff to the WHERE-clause.

EDIT: To filter on max sequence, which means you are only interested in max sequence values, you can do:

SELECT ct.*, cs.sequence
FROM CollecteEncombrants_Terrain ct
INNER JOIN (SELECT name, max(sequence) as sequenct
            FROM CollecteEncombrants_Stops 
            GROUP BY name) cs on cs.name = ct.globalid
WHERE collecteFaite is not null