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.
644
questions
-2
votes
1answer
28 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
1answer
44 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
1answer
49 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 ...
1
vote
2answers
64 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
1answer
32 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 ...
1
vote
1answer
40 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 ...
0
votes
1answer
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....
0
votes
1answer
24 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 ...
0
votes
0answers
12 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 ...
0
votes
1answer
58 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....
0
votes
2answers
115 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
0answers
34 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....
-2
votes
1answer
31 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
2answers
60 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 ...
0
votes
2answers
62 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 ...
0
votes
1answer
53 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.
...
0
votes
2answers
37 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 ...
0
votes
1answer
63 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
...
0
votes
2answers
62 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
1answer
47 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
1answer
33 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
1answer
62 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 ...
-1
votes
1answer
47 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
...
1
vote
1answer
58 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.
...
0
votes
1answer
71 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
1answer
65 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....
0
votes
0answers
40 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
1answer
26 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 ...
3
votes
1answer
99 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 ...
0
votes
2answers
72 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 (`...
0
votes
2answers
61 views
Subquery into join issue
I'm trying hard as I can to transform my query to get rid of subqueries. I know its better to not use subqueries, but my first reason to change this query is because my ORM (Doctrine) isn't able to ...
1
vote
2answers
62 views
Index isn't used when value is a result of a subquery or from a wrapper query
I have a simple one-to-many relationship: accounts and events where an account may have many events.
accounts
id: uuid
...more irrelevant fields
events
id: serial
type: enum ['activated', '...
0
votes
2answers
118 views
Limit rows in Correlated Subquery
I have one big and two small issues with my query I need help with.
First, I want a SQL (postgres if it matters) query that gets SUM of last two ratings of fruits and makes a new column for each fruit ...
2
votes
0answers
22 views
subquery for history average is slower then expected
I have a MySQL table which contains historic product values. A new record per product is updates every minute. The timestamp field is an INTEGER field with a unix timestamp. There is an index on the ...
1
vote
1answer
429 views
subquery must return onl one column in postgresql
I want to get the XML response after the stored procedure call in PostgreSQL. In the below query I was trying to get the output, but it throws an Error: subquery must return only one column. Can ...
0
votes
0answers
26 views
Appending another column onto an existing UNION
I have the following query- I am trying to add another column, however I don't want any of the "WHERE" clauses from the previous 2 SELECT statements to take affect... so I can't just input ...
0
votes
2answers
82 views
Subqueries in SQL Server
I am taking a SQL course and am completely stumped on this question.
Using a subquery have a query that returns:
CustomerID, EmailAddress, FirstName, LastName, '#3: Customers who have ordered than 1 ...
0
votes
1answer
368 views
SQL sql subquery with SUM for totals
I am a little stuck on getting a total value for a column using the SUM function. I want to get the total amount of items for an order from my database. The DB order is made up of several line items ...
2
votes
2answers
66 views
Select maximum of a count in a grouped clause
I have the following tables:
Vehicles(v͟i͟n͟, model,category)
Sales(s͟a͟l͟e͟I͟D͟, staffID,customerID,date)
vehicleSold(saleID,v͟i͟n͟,salePrice)
When I join these tables using:
select YEAR(Sales....
0
votes
1answer
48 views
Trouble with a query subselect
I am trying to merge 2 large queries together and can't get it to work. I have an almost working query, except I can't get the server to accept the p.products_id in the nested query. Can anyone help ...
1
vote
0answers
74 views
Ranking and use of cube by in an SQL query? [closed]
I am a the MSc student who does not understand this following SQL dilemma and want to be certain from an Academic standpoint.
The entire “Foodmart” database can be downloaded from: http://didawiki.cli....
0
votes
2answers
33 views
I have a correct subquery that returns results but when I use it as an "IN" clause I get error 1064
This must be a very simple problem, the subquery returns results alone but the compound query reports:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to ...
0
votes
2answers
53 views
LEFT JOIN subquery fails in the presence of an EXISTS clause in the main query
I have this query:
SELECT c.id contact_id, engagement.sent
FROM contact c
LEFT JOIN (
/* ➊ Inner query */
SELECT eq.contact_id contact_id, COUNT(*) sent
FROM mailing_job j
INNER JOIN ...
3
votes
2answers
60 views
Select rows with the highest value for one ID, after grouping by two IDs
It is given in the problem that occurrences has to happen more than once, but the person (PID) should only be selected with the (agent) AID having the highest BustCount.
SELECT I.AgentID, I.PersonID, ...
0
votes
2answers
311 views
MariaDB: Insert records (list of tuples) into table if not exists
Currently I have several sql queries that look like this:
INSERT INTO `test` (col1, col2)
SELECT * FROM (SELECT '1','2') AS tmp
WHERE NOT EXISTS (
SELECT * FROM `test`
WHERE col1='1' AND col2='...
0
votes
1answer
22 views
Having problems joining table
I have the following tables and I want to know Jack's dob, salary and the total score of that employee who is also a player.
The relational schemas are:
Emp_Players (eid,pid,team) composite key is (...
0
votes
0answers
32 views
MySQL - Counting multiple parameters from the same subquery
I have a very large MySQL (~10M rows) database, currently using the InnoDB engine, which stores log events. I am working on a front-end that allows searching through this log database, and one of the ...
0
votes
1answer
55 views
SQL - Select everything after a certain row
After the following query:
SELECT
my_table.ID,
my_table.DATETIME,
my_table.VALUE,
diagnostics_keys.type as type,
diagnostics_keys.key,
diagnostics_keys.unit,
...
0
votes
2answers
26 views
Create a DELETE statement that deletes Models that haven’t had a purchase since December 31st 2014
We are given 5 tables.
Orders
Order number (Primary Key)
Date of order
Customer ID (Primary Key for Customers table)
Serial Number (Primary Key for Machines table)
Customers
ID ...
1
vote
1answer
35 views
Create a DELETE statement that deletes Manufacturers with less than 10 sales
Orders:
Order number (Primary Key)
Date of order
Customer ID (Primary Key for Customers table)
Serial Number (Primary Key for Machines table)
Customers:
ID (Primary Key)
Name
Phone Number
Street (ie. ...