Converting SQL Server Query to MySQL Query Using Subquery Alias

Question

everyone.. i have a view in SQL Server that need to be ported to MySQL.

SELECT Geb_ID, Geb_Key, Geb_Jahr, Geb_Parzelle, Geb_Standort, 
Geb_GebArtID, Geb_BesID, Geb_boolJB, Geb_Info,
(SELECT TOP (1) Geb_BesID FROM TGebaude AS xGeb 
WHERE Geb_Key = a.Geb_Key ORDER BY Geb_Jahr DESC) AS Akt_BesID
FROM TGebaude AS a

I've tried to convert this query using LIMIT 1 (because in MySQL there is no TOP 1) but still not succeed because there is subquery using "a" alias for it's own table. is there any way to convert this query?

Answer 1

There are qualifiers missing in your subquery. (And that shouldn't really be a problem, as the inner/local table should have precedence over the main/outer one.) Apart from this I see no issue. Swap TOP for LIMIT and you should be done. Please try with the qualifiers added:

SELECT 
  Geb_ID, Geb_Key, Geb_Jahr, Geb_Parzelle, Geb_Standort, 
  Geb_GebArtID, Geb_BesID, Geb_boolJB, Geb_Info,
  (
    SELECT Geb_BesID 
    FROM TGebaude AS xGeb 
    WHERE xGeb.Geb_Key = a.Geb_Key 
    ORDER BY xGeb.Geb_Jahr DESC
    LIMIT 1
  ) AS Akt_BesID
FROM TGebaude AS a;