Learning PostGreSql: replacing TOP and APPLY with LIMIT and LATERAL

All SQL Server queries using TOP and/or APPLY need to be changed - PostgreSql uses completely different syntax.

Replacing TOP with LIMIT

The following script shows how to do that:

CREATE TEMP TABLE Runs(State_Code VARCHAR,Run_Date DATE,Distance FLOAT,Description VARCHAR);
INSERT INTO Runs(State_Code, Run_Date, Distance, Description)VALUES('IL', '2013-11-16',16.6,'2 loops in Wolf Road Woods'),
('IL', '2013-11-17',8.4,'Herrick Woods'),
('IL', '2013-11-19',7.2,'Chicago Lakefront');
SELECT State_Code, Run_Date, Distance FROM Runs
   ORDER BY Run_Date DESC LIMIT 2;  

"IL";"2013-11-19";7.2
"IL";"2013-11-17";8.4 

Replacing CROSS APPLY with LATERAL

The following script shows how to do that:

CREATE TEMP TABLE States(Code VARCHAR PRIMARY KEY);CREATE TEMP TABLE Runs(State_Code VARCHAR,Run_Date DATE,Distance FLOAT,Description VARCHAR);
INSERT INTO States(Code)VALUES('IL'), ('WI');
INSERT INTO Runs(State_Code, Run_Date, Distance, Description)VALUES('IL', '2013-11-16',16.6,'2 loops in Wolf Road Woods'),
('IL', '2013-11-17',8.4,'Herrick Woods'),
('IL', '2013-11-19',7.2,'Chicago Lakefront');
SELECT s.code, r.Run_Date, r.Distance FROM States AS s
CROSS JOIN LATERAL(SELECT Run_Date, Distance FROM Runs as r
   WHERE s.Code = r.State_Code
   ORDER BY Run_Date DESC LIMIT 2) AS r;  

---------

"IL";"2013-11-19";7.2
"IL";"2013-11-17";8.4 

Replacing OUTER APPLY with LATERAL

The following script shows how to do that:

CREATE TEMP TABLE States(Code VARCHAR PRIMARY KEY);CREATE TEMP TABLE Runs(State_Code VARCHAR,Run_Date DATE,Distance FLOAT,Description VARCHAR);
INSERT INTO States(Code)VALUES('IL'), ('WI');
INSERT INTO Runs(State_Code, Run_Date, Distance, Description)VALUES('IL', '2013-11-16',16.6,'2 loops in Wolf Road Woods'),
('IL', '2013-11-17',8.4,'Herrick Woods'),
('IL', '2013-11-19',7.2,'Chicago Lakefront');
SELECT s.code, r.Run_Date, r.Distance FROM States AS s LEFT JOIN LATERAL(SELECT Run_Date, Distance FROM Runs as r
   WHERE s.Code = r.State_Code
   ORDER BY Run_Date DESC LIMIT 2) AS r ON TRUEORDER BY s.Code, r.Run_Date;

--------

"IL";"2013-11-17";8.4
"IL";"2013-11-19";7.2
"WI";"";