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
2
votes
1 answer
Concatenation of tsvectors results in a syntax error in Postgres 9.4.6
When concatenating tsvectors from setweight in an SQL query, it throws a syntax error:
ERROR: syntax error at or near "||"
It works fine if I try it with a single tsvector returned by setweight, and if I try to wrap the entire thing in another…
Brandon Anzaldi
- 6,884
- 3
- 36
- 55
2
votes
1 answer
Query Builder Raw Lateral Join with jsonb_to_recordset
Hopefully a very simple question. I'm trying to create a query within Laravel based off of my postgresql query, and I'm having a hard time converting this one specific join because it is a lateral join utilizing jsonb_to_recordset.
join lateral…
Samantha Roseman
- 113
- 1
- 1
- 9
2
votes
1 answer
How to optimize sql query with subqueries, perhaps by lateral join?
I'm trying to optimize sophisticated sql query, it would be executed on each map bonding box change. I thought that INNER LATERAL JOIN would be fastest but it isn't. Does anybody know how to speed up this query and how to make better use of LATERAL…
luzny
- 2,380
- 7
- 30
- 64
1
vote
1 answer
Get next and previous rows ordered by column for a subset of table
I have a subset of a table:
-- Subset
+---------------------------------+---------+-----------+--+
|date_time |animal_id|location_id|id|
+---------------------------------+---------+-----------+--+
|2023-04-17 11:11:11.000000…
Shmookoff
- 146
- 1
- 10
1
vote
2 answers
Is there a way to refactor a chain of UNIONs in SQL?
I have a query like this:
SELECT DISTINCT 'EntityName' AS [Column], EntityName AS [Value]
FROM dbo.Deals
WHERE EntityName IS NOT NULL
UNION
SELECT DISTINCT 'AssetClass' AS [Column], AssetClass AS [Value]
FROM dbo.Deals
WHERE AssetClass IS…
Peter Olson
- 139,199
- 49
- 202
- 242
1
vote
2 answers
SQL (Snowflake) - Create duplicate Records changing only a single Field after duplication
I have a table that looks like below:
ID
DATE_OPENED
DATE_CLOSED
STATUS
TREATMENT
1
2022-12-05
2022-12-05
Notification
Control
2
2022-11-24
2022-11-24V
Viewed
Control
3
2022-12-11
2022-12-11
Subjecting
Control
I want to duplicate…
Blackdynomite
- 421
- 1
- 4
- 18
1
vote
2 answers
Using a where clause on a newly created field
How do i perform a where action on a newly created field that is populated with the values of two fields?
select upper(column_a + ' ' + column_b) as newly_created_field,
some_other_field
from table_xyz
where newly_created_field = 'NEW VALUE'
pithhelmet
- 2,222
- 6
- 35
- 60
1
vote
1 answer
SQL Join - Many to Many Relationship
A table of offers for different clients:
PERIOD |CELLPHONE | IDENTIFICATION | FIRST_DATE | LAST_DATE | UPSELLING | IPHONE
202208 56961424344 152783337 09/08/2022 23/08/2022 1 0
202208 56961424344 152783337 …
Lio Pecoraro
- 21
- 3
1
vote
2 answers
Find most recent (non-future) date from a list of columns
Assume a table like this (in actuality I have 50 date columns to compare):
ID
MY_DATE_1
MY_DATE_2
MY_DATE 3
1
2022-10-1
2022-11-1
2022-12-1
2
2022-10-31
2022-11-31
2022-12-31
For each record, I want to get the most recent, non-blank…
redOctober13
- 3,662
- 6
- 34
- 61
1
vote
1 answer
SQL multiple Joing Question, cant join 5 tables, problem with max
I got 6 tables:
Albums
id_album | title | id_band | year |
Bands
id_band | name |style | origin
composers
id_musician | id_song
members
id_musician | id_band | instrument
musicians
id_musician | name | birth | death | gender
songs
id_song |…
WebSurferPenguin
- 13
- 2
1
vote
1 answer
Allocate groups by size, preliminarily rounded and grouped
You are given a database of notebooks that contains two tables.
the table notebooks\brand contains data about the name of notebook brands.
the table notebooks\notebook contains data about the name of the notebook, its diagonal, width, depth, and…
Maks
- 113
- 3
1
vote
1 answer
Filter on Foreign Key with LATERAL JOIN brings yields strange results
thanks for your time!
Basically, I'm trying to filter a NxM table using foreign keys, with 0,1 or N different tags. The problem is that LEFT LATERAL JOIN yields bizarre results.
Please, don't mind the strange casting, I'm doing so because I'm using…
Leonardo
- 3,141
- 3
- 31
- 60
1
vote
0 answers
Why is group by needed for this `lateral view explode` hive query?
The following query works:
with sartab as (
select 1 id, split(concat_ws(',',map_keys(map('a',0.1,'b',0.2,'c',0.15)),
array('id_queue', 'queue_disposition')),',') sar_keys,
…
WestCoastProjects
- 58,982
- 91
- 316
- 560
1
vote
2 answers
How to use LATERAL for multiple columns?
I am using to this query to convert raster values into vector data
SELECT dp.*
, g.gid gid
, g.geom ggeom
FROM public.t32ulc_entire_tile rast
, LATERAL ST_PixelAsCentroids(rast.rast, 1) as dp
JOIN raster_grid g ON dp.geom && g.geom…
Manap Shymyr
- 149
- 7
1
vote
1 answer
Lateral join in Snowflake
I have the following lateral join that I am trying to migrate to Snowflake from Postgres
select *
from service f
join lateral
(select s.*
from inferred s
where s.patient = f.patient
order by s.from_date desc
limit 1
…
Aaron
- 1,345
- 2
- 13
- 32