I want to select only those identifiers from a table which are contained in another subquery result and only top-2 rows must be used. For example you can run the query on PG: select a.rid from( values (41), (42), (43),…
I have two tables in my database as below: categories table: Column Name Column Type ID INT Name TEXT IDParent DOUBLE and advertisements table: Column Name Column Type ID INT IDUser INT DateAdded DATETIME IDCategory INT Price DOUBLE I have…
Working in PostgreSQL, I intend to use the output of a CTE as a reference to join another CTE below it. I've used this style countless times in SQL Server and Synapse, however I'm finding it difficult to replicate in…
Query 1: WITH cte AS ( SELECT *, MAX(score) OVER (PARTITION BY exam_id) AS max_score, MIN(score) OVER (PARTITION BY exam_id) AS min_score FROM student JOIN exam USING (student_id) ORDER BY exam_id, student_id ) SELECT * FROM cte; By Query 1,…
I expected the function call "expensive" to be called only once, but it appears to be called for every row in "children". Can the CTE/query be tweaked to only call the function once and have that value returned for every…
I want to be able to call set_config to set a configuration parameter within the same transaction/scope as an INSERT statement. There is a function triggered AFTER the insert which reads from this configuration parameter. I need to accomplish this…
Given a directed acyclic graph shaped like this: "Branch A" "Branch B" branch-a-head-id branch-b-head-id --- | | ^ id-Z id-C | | | | id-Y id-B | | | "Branch A" id-X id-A and | | "Branch B" branch-a-first-id branch-b-first-id…
I have a following SQL running in PostgreSQL v13.10: WITH stuckable_statuses AS ( SELECT status_id FROM status_descriptions WHERE (tags @> ARRAY['stuckable']::varchar[]) ) SELECT jobs.* FROM jobs WHERE jobs.status = ANY(select status_id from stuckable_statuses) And it is running really slow while…
we want to "fill in the gaps" of the record_ids and fill in missing values with the most recent available value. If there is no value available, use the first available value instead. The result should look like: category record_id…
I'm working with AWS RDS MySQL and using MySQL Workbench to develop the queries before moving them into the Lambda integration function for my HTTP API on AWS API Gateway. I created this query: use prod_esports; WITH muuid AS (select…