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
-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. ...

1
2 3 4 5
13