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

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
 

renee pereira said:

Review copies available for 'Postgre SQL Cookbook'

We have limited free e-copies of our recently published book available for review.If interested in procuring a review copy,write to me at reneep@packtpub.com. Amazon account is a must.

Note: Due to limited no. of copies, only selected individuals will be provided with the copies. To know more about the book check:bit.ly/1CkxzNe

February 19, 2015 1:21 AM
New Comments to this post are disabled

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

Privacy Statement