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
-1
votes
1 answer
Merging similar SQL data for counts efficiently?
I am trying to understand the best way to merge a count from an INNER JOIN of two tables and grouping, but the best thing I can do is to query each table separately and then apply a union and further GROUP BY. This seems a bit convoluted, but with…
StefanM
- 63
- 1
- 11
-1
votes
2 answers
Split records by whether or not multiple columns have values
I need to set up a query that will split up a single record into multiple records based on values from multiple columns in a single table.
Right now, a current parcel record would read as:
table.tax_id table.CLASS 1 table.CLASS 2 table.CLASS…
Cbrown88
- 13
- 2
-1
votes
2 answers
SQL Server use the new column name in other column in view
I use SQL Server, and I created view and added new column which contain mathematical equation
Let's say I have this:
create view
as
select
a.date, a.sale, a.buy,
profit = a.sale - a.buy,
profit_prs = (a.sale - a.buy) /…
Khaled Nasser
- 23
- 3
-1
votes
1 answer
Querying pivot table to return a list of referred rows
I have these 6 tables and query which returns everything I want, but I want to add a new pivot table, so I can get a list of rows linked to it.
Query:
SELECT DISTINCT ON (pg.id, p.prod_id)
pg.group_name, p.name AS prod_name,…
user2616355
- 423
- 1
- 4
- 15
-1
votes
2 answers
Join with 'conditioned subquery'
I would like to join a 'conditioned subquery'. Let's imagine that we have a table with certain data containing an ID, a created TS and some or one value:
data_table (ID, created_ts, value) .... "652351, 2018-04-03 06:11:31.996, 1" and so on.
…
Air Zrakovic
- 1
- 1
-1
votes
1 answer
Lateral join fails on trivial example
I am trying to create a timeseries and a lag of it at the same time using lateral:
dates as (
SELECT generate_series(max(timestamp), min(timestamp), '1 week'::interval) as ts
FROM batches,
LATERAL (SELECT lag(ts) over (order by ts)) as…
mikael
- 2,097
- 3
- 18
- 24
-1
votes
1 answer
Updating a table column with the 2nd oldest date in a row?
I would like to update the contents of the Date 1 column to reflect 2nd oldest date each row. The table has thousands of rows and ~15 columns, only a handful of which are dates. I've used the least funtion in the past to update this column with the…
Tboi
- 57
- 5
-1
votes
1 answer
Return row with unique values across columns of same table
I have the data like…
jayesh kshirsagar
- 23
- 4
-1
votes
2 answers
How to select biggest zip code for each country
In each country, which city has the highest ZIP code?
Select only the Country name, and the city name
Here is a graphic schema, which may help you:
This is what I done so far:
SELECT CountryName, CityName
from City ci
join County co on…
StereoMC
- 103
- 8
-1
votes
1 answer
SQL - In a week get result count of records in that week and count of records ageing 7days from that week
This is redshift SQL
I'm trying to get 2 results for a week:
Total records in that week
Total records ageing greater than 7 days from that week.
say there are sample 100 records in below format, in current example 7 records/week:
day code …
newbie93
- 3
- 2
-1
votes
1 answer
SQL Server calculating percentage when using cross apply
Using below query i am trying to get how many users are falling under which age group, for this i applied cross apply.
SELECT y.AgeDecade, COUNT(u.[UserId]) AS [TotalUsers]
FROM dbo.[User] u
CROSS APPLY (SELECT Age = (CONVERT(int,…
adityaa
- 111
- 2
- 2
- 10
-1
votes
2 answers
SQL Server - Reuse the value returned by the subquery without executing the subquery again
I use a subquery in my select query which gives me an aggregate value, the subquery is of 4-5 lines and involves joins and conditions.
Now i need to use this value returned by the subquery in my outer query to do some calculation.
SELECT…
claudia
- 23
- 1
- 7
-1
votes
1 answer
Get the earliest of a date-value pair in T-SQL
I am trying to build a query in t-sql to add a custom date-value pair column. My data contains the project ID, L Date and L Status, N Date and N Status. I know how to add the earliest date using min function. How do I capture the earliest date and…
rustynails
- 119
- 4
- 12
-1
votes
2 answers
Insert 3 columns in 1 columns SQL
I have an old table with 3 columns (COLUMN01, COLUMN02, COLUMN03) and I need to migrate to a new table which contains 1 column (VALUE) how to do? I tried a Union but it does not work ...
Prometheus
- 3
- 6
-1
votes
1 answer
PostgreSQL Lateral join when right table row doesn't exist
When I have in my DB a category without posts, the resulted select returns rows without an empty category. How to do receiving a table with empty category.
SELECT
c.id as category_id,
pp.id as post_id
FROM categories c,
LATERAL
…
Ulianka Shu
- 133
- 1
- 12