224 questions
0
votes
2
answers
80
views
SQL filtering with lateral join on key and path
I am querying a database from a table of product IDs and location codes, stored in the nested format below. I'm trying to write a query to filter for only products that exist in the location South3. ...
- 221
1
vote
0
answers
58
views
What is the purpose of ON TRUE when doing a LEFT JOIN LATERAL statement here? [duplicate]
I have the following query statement, but why the on true is required is unclear.
[question]
id
text
1
Boba Fett is a...
[question_option]
id
question_id
text
correct
1
1
Jedi
false
1
1
Sith
false
1
1
...
- 41
0
votes
1
answer
118
views
Left join later flatten works not like left join, but the same as inner join
With this data:
create or replace table test_json as
select
parse_json('{
"include": [
"x",
"y",
"z"
...
- 45
0
votes
0
answers
66
views
Using lateral join from a subquery in sqlalchemy
I have 2 tables (defined as models in python): Productions and Tasks.
A Production can contain 1 or more Tasks.
Tasks have a name (string), a priority value (int) and a state (TODO / ACTIVE / COMPLETE)...
0
votes
3
answers
688
views
Performance loss introducing lateral join in Postgres query
I am dealing with a PostgreSQL (v14) query of this kind
SELECT
...,
EXISTS (
SELECT 1
FROM table2 t2
WHERE t2.fk = t1.id
AND LOWER(t2.table_name) = 't1'
) ...
- 15
0
votes
1
answer
82
views
Postgresql lateral join on condition evaluation order
Hello I am having problem with postgresql lateral join. My query is this:
SELECT *
FROM foo
LEFT JOIN LATERAL (SELECT tsrange(now() at time zone 'utc', expiration_date) as time_range)
...
- 53
-1
votes
1
answer
75
views
Use results from one query in WHERE clause of the next query
This is how it currently works, first select query returns a list of parameters.
SELECT character_id, foe_id, location_id, date_time, damage, points
FROM events
ORDER BY date_time DESC
LIMIT 100
...
- 15
2
votes
1
answer
98
views
For each row in table A, update A.amount related rows in table B
Using PostgreSQL 16.2, I have two tables:
task_pool table
create table task_pool (
task_id serial primary key
, order_id integer
, user_id integer
, expire_time timestamp
, status integer default 0
)...
- 75
0
votes
0
answers
66
views
Is it possible to use a table alias generated in a lateral subquery in another lateral subquery?
The doubt arises from a scenario like this:
I have a table named one_table that has many related rows in another table named another_table, via foreign key like this another_table(one_table_id) ...
- 3,998
1
vote
1
answer
125
views
Lateral Join returns too many rows
I try to understand LATERAL JOIN with this query:
select m1.contributor_prescription, derniere_publication.date_publication_prescription
from activite.metadonnee m1
left join lateral
(select ...
- 177
0
votes
1
answer
152
views
snowflake query is taking too much time to run
SELECT
category_tree_master__."category_id" AS category_id,
--searchchildrenmaster.children,
--ARRAY_CONTAINS(category_product_nb_stand_nb_product."category_id" ::...
0
votes
1
answer
101
views
KNN Across categories in postgis using indexing
I have a dataset of points of different types. For every point in the dataset I want to find the closest point in every category. I can achieve this but the compute time is very long and I'm ...
6
votes
2
answers
4k
views
Why is a LATERAL JOIN faster than a correlated subquery in Postgres?
I have rewritten the below query from using a correlated subquery to using a LATERAL JOIN. It is now faster, and I would like to understand why?
This SO answer says that there is no golden rule and ...
- 1,972
0
votes
1
answer
837
views
LATERAL JOINS Causing Performance Issues
This query is for a Tableau dashboard. The users want to see the active clients in a sector as well as the contacts at those firms and how the composition of the data changes from quarter to quarter. ...
- 335
6
votes
2
answers
4k
views
ON predicate of Postgres LATERAL JOIN
How does ON predicate of Postgres LATERAL JOIN work?
Let me clarify question a bit. I've read the official documentation and a bunch of articles about this kind of JOIN.
As far as I understood it is ...
- 311
0
votes
1
answer
101
views
Snowflake Equiv of PySpark STACK (LATERAL VIEW)
I have working code using PySpark which uses the STACK function to turn columns into rows, creating a display_name column in doing so.
I am trying to convert this to a Snowflake query, in theory my ...
- 69
0
votes
2
answers
343
views
lateral join with a right join
so I have two tables, A and B.
first I do a lateral join of A with one column (items)
select A.id, item->>'id' as item_id, b.id
from a, jsonb_array_elements(items) rx(item)
then I do a right ...
- 187
1
vote
1
answer
420
views
Oracle: Syntax for multiple lateral joins or applys
I am trying to get the syntax for a series of lateral joins in Oracle.
I have an authors table with the author’s id, name and home; I have a books table with an id, authorid which is a foreign key to ...
- 17k
0
votes
0
answers
52
views
Can LATERAL JOIN have a pivot table subquery in MariaDB 10.6? [duplicate]
I am trying to join data from two tables by nearest earliest date. The first tables dates are more frequent than the seconds dates. I came across a few links and it seems like a Lateral Join may ...
- 95
0
votes
2
answers
64
views
Pivot wide to long cannot find table name
I am running the following cross apply sql query, however, the query cannot find the table.
For example:
SELECT sport,event_names
FROM table_name t
CROSS APPLY
(
VALUES
(event_1),
...
- 351
2
votes
4
answers
367
views
SQL: how to limit a join on the first match with criterias?
Here are the exemple tables:
Product Prices (prd)
start_date
product
price
2023-04-01
prod_A
10.0
2023-04-15
prod_A
20.0
2023-04-01
prod_B
20.0
Order Products (ord)
order_date
id
product
2023-04-01
...
1
vote
1
answer
86
views
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|
+---------------------------------+------...
- 58
0
votes
1
answer
543
views
Transpose sql query result - Posgresql
I've a below result set.
Id Name Age
1 abc 3
Required Result:
Id 1
Name abc
Age 3
- 141
0
votes
1
answer
200
views
SQL query self-join without CTE or temp
Background
I have a select statement that churns a bit. For sheer simplicity sake, we'll say it looks like this:
select AllTheThings, AndThenSome
from T1
The output looks like this:
AllTheThings
...
- 353
0
votes
2
answers
151
views
How can I transpose the row of a table as well as the column names in mysql?
I have a table which has only one row like below,
Here I want to convert the column names to one column and the row into another column, with the column names converted to some predefined numbers as ...
- 417
0
votes
0
answers
69
views
Understanding LATERAL in Postgresql [duplicate]
Would like to know what is LATERAL and why LATERAL in SQL? Have few question to understand more on LATERAL. I searched in google and not getting clear understanding of LATERAL.
Why Lateral
Advantage ...
- 3,058
0
votes
1
answer
624
views
OUTER APPLY REMOVE DUPLICATES
I have used outer apply to pull the most recent careID and diagnosis date and then used aggregation function to count the total. The code worked, however as soon as I added another join which is:
...
user21456292
0
votes
2
answers
106
views
Sql statement to test two columns and get results in one column without duplicating rows
I have been trying to solve how to test conditions in two different rows, and put the results into one singular column without duplicating rows.
I need to get the results in a way that pick the team ...
- 1
1
vote
2
answers
185
views
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 [...
- 144k
0
votes
2
answers
50
views
Is recursive query an option for concatenating text for building a path
Simplifying a bit. I have the following data structure:
| id | name | traversal_ids[] |
| -------- | -------------- |-----------------|
| 1 | container1 | {1} |
|...
- 39
0
votes
1
answer
415
views
Calculate rolling average of a measure based on certain condition from another column
Below is the given table.
date
id1
id2
fact
boolean
2022-01-01
i1
l1
100
0
2022-01-02
i1
l1
10
1
2022-01-03
i1
l1
110
0
2022-01-04
i1
l1
70
0
2022-01-05
i1
l1
20
1
2022-01-05
i1
l1
30
1
2022-01-06
i1
...
2
votes
5
answers
99
views
How to search in columns in SQL
I have two table,the names table and the earnings table.
Name_ID
Name_desc
1
mark
2
smith
3
becky
4
jimmy
and the earnings table
ID_1
Earnings_1
ID_2
Earnings_2
ID_3
Earnings_3
ID_4
Earnings_4
1
500
2
...
-1
votes
1
answer
40
views
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 ...
- 63
0
votes
1
answer
136
views
PostgreSQL table transformation
I have the table in below format :
id
year
column1
column2
column3
1
2000
10
20
30
1
2001
100
200
300
I want the transformed version to be like -
id
Field
value_2000
value_2001
1
column1
10
100
1
...
- 3
1
vote
2
answers
2k
views
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
...
- 431
0
votes
1
answer
145
views
How To Properly Use Lateral Joins In PSQL
When I run the command below, there is a syntax error highlighted at UNION on the last line. I am really not sure why.
SELECT *
FROM (
SELECT (final_exp.deploymentyear + number) ...
5
votes
3
answers
907
views
SQL query for finding latest or max value of timestamp from table corresponding to N ids Timescaledb
I have a table tab1 in timescale db which has 3 columns tag, time, value. time and tag make up the pk for the table: (time, tag).
There are more than 500 lakhs (50 000 000) of rows. I need to find the ...
- 113
3
votes
2
answers
164
views
Minus 1 unit if the condition is met in mysql
I need some help here, my request is :
List the bookID and number of copy of this in Library (Number of copy in library = number of copy – number of copy student borrow but not return yet)
and this is ...
- 41
1
vote
2
answers
83
views
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 ...
- 2,312
1
vote
1
answer
644
views
SQL join on the nearest less date
I am dealing with a problem in Postgres, where I have table ORDERS with column RECEIVED_AT and table CURRENCY_RATES with column VALID_FROM. There are similar questions on StackOverflow, but ...
0
votes
1
answer
586
views
Postgres select item from json in json_agg
with a as(
SELECT '[{"residential" : 100}, {"childcare" : 200}, {"open space" : 50}]'::jsonb t
)
select t->'childcare' from a
this keeps returning null when it should ...
- 1,558
1
vote
1
answer
120
views
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 ...
- 21
1
vote
2
answers
547
views
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 ...
- 4,066
0
votes
1
answer
502
views
How to replace complex lateral join subquery with window functions in Postgres?
We're having some logic to detect duplicate posts in out system. The query looks similar to this:
select *
from posts outer lateral join
(select id
from posts where id != outer.id and
(tag = outer....
- 379
1
vote
1
answer
35
views
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 | ...
0
votes
2
answers
222
views
Split multiple columns into rows by delimiter comma
I have one table (Bigquery), which has ', ' separated values in two columns:
id tag_id tag_name
1 1, 2, 4 a, b, d
2 3 c
3 1, 4 a, d
For each tag_id is 1:1 ...
- 285
1
vote
1
answer
31
views
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 ...
- 113
-1
votes
2
answers
55
views
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....
- 13
1
vote
1
answer
342
views
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 ...
- 3,201
0
votes
2
answers
527
views
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 -...
