THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

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 TRUE
ORDER BY s.Code, r.Run_Date;

--------

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

Published Tuesday, November 19, 2013 12:06 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Hans said:

Thanks, your blog is really helpful.

A minor nitpick though:

It's either Postgres or PostgreSQL.

There is never a capital G in the name (no PostGre, no PostGres, no PostGreSql)

April 8, 2014 10:51 AM
 

Alexander Kuznetsov said:

Hans,

Thank you for the correction. I will fix it now.

April 24, 2014 3:41 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement