Wednesday, March 12, 2014

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_CodeRun_DateDistanceDescription)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_CodeRun_DateDistance 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_CodeRun_DateDistanceDescription)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.coder.Run_Dater.Distance FROM States AS s
CROSS JOIN LATERAL(SELECT Run_DateDistance FROM Runs as r
   WHERE s.Code r.State_Code
   ORDER BY Run_Date DESC LIMIT 2AS 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_CodeRun_DateDistanceDescription)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.coder.Run_Dater.Distance FROM States AS LEFT JOIN LATERAL(SELECT Run_DateDistance FROM Runs as r
   
WHERE s.Code r.State_Code
   
ORDER BY Run_Date DESC LIMIT 2AS ON TRUEORDER BY s.Coder.Run_Date;
--------
"IL";"2013-11-17";8.4
"IL";"2013-11-19";7.2
"WI";"";

No comments:

Post a Comment