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.
662
questions
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, @...
- 11
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',
...
- 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, ...
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 ...
- 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,...
- 11
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 ...
- 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 ...
- 3
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 ...
- 1
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 ...
- 3
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 ...
- 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
...
- 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 ...
- 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 ...
- 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".&...
- 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 ...
- 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
...
- 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 ...
- 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 ...
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
...
- 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, ...
-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) ...
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 ...
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 ...
- 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 ...
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 ...
- 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 ...
- 21
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....
- 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 ...
- 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 ...
- 101
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....
- 13
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 ...
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....
- 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 ...
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 ...
- 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 ...
- 11
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.
...
- 41
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 ...
- 101
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
...
- 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 ...
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
...
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:...
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 ...
- 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
...
- 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.
...
- 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 ...
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....
- 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 ...
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 ...
- 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 ...
- 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 (`...
- 4,261