LATERAL Derived Table
A LATERAL derived table is a subquery in the FROM clause that can reference preceding JOIN-ed tables (in the same FROM clause.)
Example:
SELECT ... FROM t1 JOIN LATERAL (SELECT * FROM t2 WHERE t2.cx = t1.cy) tl ON ...
Questions tagged [lateral-join]
212 questions
0
votes
2 answers
Saving only unique datapoints in SQL
For simplicity: We have a table with 2 columns, value and date.
Every second a new data is received and we want to save it with it's timestamp. Since the data can be similar, to lower usage, if data is the same as previous entry, we don't save…
Boris
- 3,163
- 5
- 37
- 46
0
votes
1 answer
Calcul (multiplication) of two select-result
I'm trying to multiply two numbers I got from a SELECT statement of a unique query. I want to get the number of providers and the number of proposals (the query I made displays that), and multiply both on the same line (that I can't do).
I've made a…
ordiminnie
- 97
- 10
0
votes
1 answer
Group by limit per group (PostgreSQL)
I have following query:
WITH relationships AS (
SELECT related_user_id, count(*) AS trade_count
FROM trade_history
WHERE user_id = 487834568
GROUP BY related_user_id
ORDER BY trade_count DESC
)
SELECT offers.*,
…
messy
- 915
- 6
- 26
0
votes
4 answers
SQL to get most recent value as of each date in the past
I have a table called event_user_fav_color_changed. Every row in the table represents the event that a user changes their favorite color. For every date in a certain range, I'd like to get every user's favorite color as of that date.
Here's a sample…
ASDFQWERTY
- 399
- 4
- 8
0
votes
2 answers
Is it possible to get column value with subquery based on column's key?
I have the following table:
id | fruit | parent |
----------------------------
id_1 | apple | |
id_2 | | id_3 |
id_3 | pineapple | |
id_4 | plum | id_5 |
id_5 | plum | |
Is it possible…
tmsblgh
- 517
- 5
- 21
0
votes
0 answers
Why did my SQL Query Grow the DB and Fill the Disk?
I created a query for a client and I've been tweaking it trying to get it just right for their needs. I had it almost set except for some odd counts. After a serious FACEPALM moment I realized that the DISTINCT in the COUNT and SUM were throwing…
Dizzy49
- 1,360
- 24
- 35
0
votes
1 answer
Postgres LATERAL Query Correctness and Efficiency
I have the following structure of data, with table names give in bold font and their pertinent column names below.
common_authorprofile:
{id, full_name, description, avatar_id, profile_id}
aldryn_people_person table:
{id, phone, ...}…
MadPhysicist
- 5,401
- 11
- 42
- 107
0
votes
1 answer
Using Lateral Flatten and Join results in a syntax error
I'm running into an issue where I get a syntax error if I try to join and lateral flatten in the same query. I've created the following example:
Setup
CREATE TABLE "DEMO_DB"."PUBLIC"."MAIN_TABLE" (id number, department_id number, stuff…
Chip Ach
- 9
- 1
0
votes
1 answer
PostgreSQL: Find the rows in one table based on the maximum of associated records in other tables
I have the below tables
vehicles - id, name
vehicle_settings - id, settings, vehicle_id, company_id, updated_at, updated_by
vehicle_licenses - id, license_number, vehicle_id, company_id, updated_at, updated_by
users - id, name, email
One vehicle can…
Surya
- 2,429
- 1
- 21
- 42
0
votes
0 answers
sqlalchemy and postgres: lateral expression without from clause
I'm trying to do smth like this, but with sqlalchemy:
select t1.a, t2.x, l1.a_x, l2.a_x_y
from t1
join t2 on t1.id = t2.id
join lateral (select t1.a + t2.x as a_x) l1 on true
join lateral (select l1.a_x + t2.y as a_x_y) l2 on true
Problem…
gistart
- 113
- 7
0
votes
1 answer
Get count of related rows in postgres with existing lateral join
This query gives me several rows from table1 along with the related records from table2 using left join lateral. I've limited the rows from table2 to 10 rows each.
select t1.id, array_agg(t2.column1)
from table1 t1
left join lateral (select * from…
user779159
- 9,034
- 14
- 59
- 89
0
votes
2 answers
Syntax: PostgreSQL with lateral join to stored procedure
PostgreSQL 11.1
Where I have a procedure defined as:
FUNCTION Copy_Name( to_keep_id integer, to_delete_id integer, parent_table text) RETURNS VOID
Can I use it with a cte like:
WITH _in (to_keep_name, to_del_name) AS (
VALUES ('tom',…
Alan Wayne
- 5,122
- 10
- 52
- 95
0
votes
1 answer
Convert OUTER APPLY To Oracle 11g statement
I have the following TSQL,
select 2*EC.Total C1, T1.C2, EC.Total
FROM MyTable1 T1
OUTER APPLY (select COUNT(*) AS Total from MyTable2 T2 WHERE T1.SomeColumn=T2.SomeColumn ) EC
ORDER BY EC.Total desc
I need to convert this to Oracle 11g. I tried…
Imran Qadir Baksh - Baloch
- 32,612
- 68
- 179
- 322
0
votes
1 answer
PostgreSQL poor lateral join performance
I have tables
dn_table ~ 10_000 rows
| DN |
--------
| 1234 |
| 1235 |
| .... |
sr_table ~ 1m rows
| SR |
--------
| 2345 |
| 2346 |
| .... |
And I stuck with a lateral join query for them. It has extremely bad performance some queries are…
cudouny
- 124
- 7
0
votes
1 answer
Why is my postgres lateral subquery failing?
I'm trying to run the following query with postgres's support for LATERAL subqueries:
with s as
(
select
tagValues ->> 'mode' as metric
, array_agg(id) as ids
from
metric_v3.v_series
…
Dewet Burger
- 3
- 2