Questions tagged [subquery]

SQL term used to describe when a `SELECT` statement is used as part of a larger SQL statement. The larger statement may be DML and is always found within brackets or parenthesis.

Filter by
Sorted by
Tagged with
1 vote
0 answers
24 views

Return back only top N rows for each ID - SQL

I have written this SQL after browsing different topics. The topic I got the solution from -> Limit Records MySQL version: 5.6 SELECT op.* FROM (select op.*, (@rn := if(@s = order_id, @...
user avatar
0 votes
1 answer
10 views

Mysql: sorting rows of a subquery generated table not working working

I have a subquery (generated by an ORM for pagination purposes) that generates a table that is then sorted by the parent query. SELECT DISTINCT `distinctAlias`.`invoice_id` AS 'ids_invoice_id', ...
user avatar
  • 7
0 votes
0 answers
15 views

MySQL Creating Unnecessary Sort Index When an Aggregate Query is a Subquery

I have a complicated aggregate query, using the functions SUM and GROUP_CONCAT, that starts returning results in a reasonable amount of time by itself. But when this query is wrapped in another query, ...
user avatar
1 vote
1 answer
40 views

How do I SELECT elements using WHERE with another table's record values in WHERE statement?

Problem summary Today part of my app crashed, but nothing else stopped with it. I need to find the first and last tracked message ids for each channel during the downtime. I log unix timestamps ...
user avatar
  • 13
1 vote
0 answers
52 views

Postgres: one-to-one join with duplicates and inequality matching

** Update: Fiddles now included https://dbfiddle.uk/?rdbms=postgres_14&fiddle=0c618e64c7fcd54c8781ed7d1ac540f0 and https://sqlfiddle.com/#!17/12053 ** I have two tables taken from two data sources,...
user avatar
0 votes
1 answer
25 views

MYSQL SELECT if else in a subquery with default value in case that a value doesn't exit

I have the next scenario, DB: px tables: categories category_for_level clients_ip_to_user full levels types user I need to check if there is a "url" in the full table which is in one of the ...
user avatar
  • 1
0 votes
2 answers
40 views

How to query through foreign keys while only having the value in the the same row as the value used as foreign key in another column

db admins, I require your help! My experience working with databases directly using SQL is rather limited, since I've mostly been using ORMs to cover all my needs as a frontend developer, but this ...
user avatar
0 votes
1 answer
23 views

Finding and locally deleting deleted records in a million-record table

I'm trying to incrementally load data from a remote server to a local one (using SSIS and linked server). Remote table has 1.7 million of records, increasing every hour. So far, I have been able to ...
user avatar
0 votes
1 answer
28 views

How to get the highest (max) cost, lowest (min) cost, and the date that each occurred?

I need to find the highest cost & lowest cost for each part in a table (There are many parts) in a given date range. And output the date that each cost was recorded. Example Data: CREATE TABLE ...
user avatar
1 vote
1 answer
61 views

Inconsistent results with MySQL, query never ends on MariaDB

We have some really strange behaviour on MySQL (8.0.29) that we can't explain. We have 2 tables + 1 link table in-between, illustrated by this schema : We run a query whereby we need to fetch the id ...
user avatar
  • 107
0 votes
1 answer
56 views

How to speed up subquery

Let's suppose we have: select o_orderpriority, count(*) as order_count from orders where o_orderdate>='01/07/1993'and o_orderdate<'01/10/1993' and exists (select * from ...
user avatar
  • 95
0 votes
0 answers
15 views

Finding duplicate records in a subset

I have 3 tables: users orders order_items I am trying to get a list of all orders by the same user that have the exact same order_items.item_id within a 24 hour period representing the user having ...
user avatar
  • 359
0 votes
1 answer
66 views

How do I get the sum of a field and avoid duplication from an inner join?

I am pulling Geo from the Accounts table and I am ranking the Geos by Opportunity $$ Amt based on Accounts that have the name like "Brinks%". Accounts are unique, and there can be many ...
user avatar
  • 3
6 votes
1 answer
740 views

Unused columns in WHERE clause

I found this query in our codebase: DELETE FROM "Foo" WHERE ("Foo"."Id", "Foo"."CreatedAt") IN (SELECT "f"."Id", "f".&...
user avatar
  • 175
0 votes
1 answer
65 views

SQL Main query value used in the WHERE in the subquery

I'm really confused by this SQL here, I have this sample where I am using the value from the main query in the subquery and then using XML PATH to add a comma between the dates. The setup is 2 tables ...
user avatar
  • 145
0 votes
1 answer
37 views

What is the correct usage of IN and ORDER BY keywords when used in conjunction with subqueries?

ERD for my simple db design: I want to order children based on their parent's last name. I am trying to use the following query: SELECT * FROM children WHERE parent_id IN (SELECT parent_id ...
user avatar
  • 127
0 votes
0 answers
43 views

Matching against a ruleset, and visualisation by crosstab

This question is "inspired" by another question at https://stackoverflow.com/questions/66165290 about "matching on a NULL". To summarise, they wanted to provide a partial row, and ...
user avatar
  • 11
-1 votes
1 answer
99 views

SQL query to join on maximum value on a per-row basis

I'm stuck writing a query for the following scenario: I have a table of things, they have a metric called number in the examples below. I have another table called offers which is a list of offers ...
user avatar
0 votes
1 answer
69 views

how to use distinct on to avoid aggregating column in this query?

what I am trying to do is I want to calculate the quantity for the inventory the formula for that is receive_order_entries.quantity+ the sum of all inventory_transactions.quantity for that inventory ...
user avatar
  • 13
0 votes
1 answer
59 views

Create a select query to get employee name, total salary of employee, hobby name(comma-separated - you need to use subquery for hobby name)

see the below query table. Create an “employee” database and 4 tables (hobby, employee, employee_salary, employee_hobby). hobby: id, name employee: id, first_name, last_name, age, mobile_number, ...
user avatar
-2 votes
1 answer
93 views

SQL Subquery not working in Redshift

I'm trying to calculate report for ratio per week. Ratio = Sales_Dollars/Inventory_Dollars Sales_dollars per week and Inventory_dollars per week are calculated separately in 2 different queries (A, B) ...
user avatar
0 votes
1 answer
47 views

Sum of Total per Client

I want to know the total of each product that each customer has bought and I don't know how to add the total per customer. The tables are as follows: CREATE TABLE CLIENTES -- Customers ( CODIGO ...
user avatar
0 votes
1 answer
65 views

How can I improve the run time of a query that groups / display same date data in one row?

I currently have a view that contains about 1k rows of user information. currently i have a query that groups all the same user information by date into one row. Currently this query takes between 20 ...
user avatar
  • 15
1 vote
2 answers
328 views

Postgres slow exists subquery

I have a queue system that has two tables, jobs and jobs_queues. jobs is where we put jobs to run and job_queues we populate with queue names so that when we pick jobs we can discard those that are ...
user avatar
0 votes
1 answer
101 views

Understanding why getting each parent row joined with its latest child is not working when using subquery filter

I have a parent-children tables relationship, and I was trying to get each parent row with its latest child row details. My attempts to do that using a sub-query didn't work, and I could only made ...
user avatar
  • 351
1 vote
1 answer
67 views

Postgres v10 is not using the index in a subquery

I have a subquery that will return 3 records, to later add the result of some calculations (using some functions). When I am running the full query, Postgres is calculating a high number of expected ...
user avatar
0 votes
1 answer
26 views

Return all archive records when any one record meets criteria

I want to get all archive records related a transfer ID where at least one archive record has a date in the report month (in this case September). The Archive Table SELECT PTA.PlnndTrnsfrID, PTA....
user avatar
  • 11
0 votes
1 answer
28 views

Improving JOIN with sub-query

I capture Users who comment on the Articles of other Users, as INSERT IGNORE INTO AuthorCommentators (AuthorID,CommentatorID,Freq) SELECT AuthorID,CommentatorID,C FROM (SELECT b.UserID ...
user avatar
  • 4,261
0 votes
0 answers
35 views

Subquery works efficiently until I add order by and limit

https://stackoverflow.com/questions/69928299/subquery-works-efficiently-until-i-add-order-by-and-limit?noredirect=1#comment123612719_69928299 Sorry I'm new to SQL and this question is for PostgreSQL ...
user avatar
0 votes
1 answer
143 views

SQL Insert by subquery inside update query [closed]

I want to Write a single query that updates columns in one table and insert rows in another table. something like this update applications set applications....
user avatar
0 votes
2 answers
172 views

MySQL multiple subqueries - slow ordering

Any idea how to optimise this query? It runs very slow when using the ORDER BY (~10 seconds) clause. If I remove the order_by clause, then it executes for milliseconds. Problem is, that I need to been ...
user avatar
0 votes
0 answers
42 views

How to avoid subquery duplication? Getting multiple rows of subquery

I need to create a view like this: CREATE OR REPLACE VIEW bayer_pss_dashboard AS SELECT m.machine_id AS machine, t.name AS time_interval, ( SELECT SUM(TIMESTAMPDIFF(MINUTE, me....
user avatar
  • 101
-2 votes
1 answer
36 views

Having trouble with where subquery count

Assume all of the table and field names are correct. I've tried this a few different ways but this is the only that makes sense needing the join in both. This is the question: SQL query to get the ...
user avatar
0 votes
2 answers
651 views

Update with subquery referencing the main table

I'm trying to understand how to filter my subquery in context to the main query. Ultimately I'm trying to get the MAX value from the latest record BEFORE the date of the current record. Here is where ...
user avatar
  • 1
0 votes
2 answers
367 views

Query with CASE in subquery join clause is very slow on PostgreSQL but fast on Oracle and MariaDB

We have an attribute-oriented data management system with bitemporality and flexible tenant priorization logic. Deletion of records is done by inserting the same row in DELETED status. Tenants may ...
user avatar
0 votes
1 answer
115 views

How does the internal function to_char in PostgreSQL work?

I am working in an Oracle to PostgreSQL migration project. Today I found what I think is a PostgreSQL bug. I wonder whether I missed something in my experimentation. The following is my test code. ...
user avatar
0 votes
2 answers
48 views

Store value of subquery in update for later use

Trying to calculate % change of price. Query below works, but was hoping there was a way that I didn't have to call the same subquery first? I don't think WITH works here unless I'm mistaken. update ...
user avatar
0 votes
1 answer
167 views

SELECTing n rows for each GROUP with WHERE condition

I SELECT n rows for each ArticleID from TagMap table SELECT a.ArticleID,a.TagID FROM TagMap a LEFT JOIN TagMap b ON a.ArticleID = b.ArticleID AND a.TagID < b.TagID GROUP BY a.ArticleID,a.TagID ...
user avatar
  • 4,261
0 votes
2 answers
86 views

Query slow when a sub select is used

I've created a query which brings back all revenue for specific groups with different columns for YTD, Last year, the year before and the year before that. It works but extremely slowly and from what ...
user avatar
0 votes
1 answer
121 views

Group by bit column

I want Group by rows with active column like result I tray with this code and I Do not get result WITH CTE as ( SELECT RN = ROW_NUMBER() OVER (ORDER BY id), * FROM test ) SELECT ...
user avatar
1 vote
1 answer
58 views

basic subquery evaluation

I am new at subqueries. Is there a general strategy on how basic subqueries are evaluated and executed (besides from sql engines automatically optimizing them). For example, consider this select query:...
user avatar
0 votes
1 answer
79 views

using column in sub query within SELECT [closed]

I have the following SQL that uses column inside a SELECT within a SELECT in the FROM clause. It looks like it is not supported. ATM I see no alternative beside splitting this into two queries. Users ...
user avatar
  • 425
-1 votes
1 answer
208 views

Get latest record with max and group by

I am working on a php application and need to retrieve the latest record from a mysql table. Below is the table design and sample data. So the criteria is: sort by present DESC, then start_date DESC ...
user avatar
  • 379
1 vote
1 answer
64 views

Is there anything I can do to optimise this complex query?

I have a rather complex query that spans several layers of subqueries, the two primary ones fetching data from the exact same fields, only one takes the values themselves, the other takes aggregates. ...
user avatar
  • 151
0 votes
1 answer
113 views

use row_number() over(partition by col order by col) in where clause [closed]

I have these tables in which I need to use Row_number() in where clause like this where spectrumcms.staffchilddiscountpolicy.childno = (select ROW_NUMBER() Over (Partition by std.GuardianCNIC order ...
user avatar
0 votes
1 answer
101 views

Using subquery in WHERE makes query extremely slow

I have this rather basic query that is very slow for reasons I can't figure out: SELECT s.id FROM segments s WHERE ST_DWithin( s.geom::GEOGRAPHY, ST_Envelope((SELECT ST_COLLECT(s2....
user avatar
  • 193
0 votes
0 answers
57 views

Postgres locking issue

I have the following Postgres table: CREATE TABLE my_db.url_data ( url varchar(2500) NOT NULL, status int, priority int, ... last_update_ts timestamp, CONSTRAINT url_data_pk ...
user avatar
0 votes
1 answer
31 views

Querying both ancestors from a bridging table getting cross join?

Hi I have the following ERM, kit table component table kit_has_substitutes - bridging table that links a kit to two components, intended to represent the situation where a primary component can be ...
user avatar
  • 9
3 votes
1 answer
141 views

Understanding the relationship between a Table Spool and correlated subquery

I'm learning about execution plans, and have a question regarding the relationship between a Table Spool and correlated subquery (I'm following this tutorial, don't mind the typos). I've created the ...
user avatar
  • 351
0 votes
2 answers
76 views

How to UPDATE faster with GROUP BY?

I have a typical article and tag_map tables, CREATE TABLE `articles` ( `ID` int(11) unsigned NOT NULL, `Tags` tinyint(3) unsigned DEFAULT NULL, `Title` varchar(512) DEFAULT NULL, PRIMARY KEY (`...
user avatar
  • 4,261

1
2 3 4 5
14