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.
716 questions
0
votes
1
answer
2k
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,531
0
votes
2
answers
159
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
663
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
404
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:...
1
vote
1
answer
952
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 ...
- 533
-1
votes
1
answer
2k
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
...
- 369
1
vote
1
answer
93
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
1k
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
404
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
135
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
75
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
1k
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 ...
- 457
0
votes
2
answers
90
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,531
0
votes
2
answers
143
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
2
answers
128
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', '...
- 111
0
votes
2
answers
1k
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
0
answers
33
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 ...
- 121
1
vote
1
answer
2k
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
2
answers
698
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
1
answer
3k
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 ...
- 1
2
votes
2
answers
1k
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....
- 45
0
votes
1
answer
113
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
1
vote
0
answers
131
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....
- 11
0
votes
2
answers
165
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 ...
- 11
0
votes
2
answers
1k
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 ...
- 427
4
votes
2
answers
2k
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
2
answers
1k
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
1
answer
25
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 (...
- 45
0
votes
0
answers
130
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 ...
- 3
0
votes
1
answer
1k
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
2
answers
73
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
1
answer
38
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. ...
0
votes
1
answer
1k
views
Debit Credit Balance calculating from previous records with Pagination MYSQL
I have table with the following schema and demo data-
id date particulars debit credit
104 29-12-2020 Crediting NULL 1000
105 29-12-2020 Purchased Item:A ...
- 3
-3
votes
1
answer
297
views
Change Consecutive Row Value [closed]
I am trying to change value of Consecutive AA to first AA as AA1 and second one as AA2, if only one AA then it should be as AA1, its possible to change all AA to AA1 and then search for 2nd ...
1
vote
1
answer
103
views
Query to find the "Number of days" a customer has an active card between a specified interval
I have a table with below structure :
create table test_table
(
customer_num NUMBER,
card_number char(2),
card_issue_date DATE,
card_expire_date DATE
)
Example data is:
...
- 1,486
0
votes
1
answer
383
views
SQL (Postgres): Subquery in WHERE clause with IN operator is slow
I have the following SQL Statement:
select count(*)
from person p
cross join task t
where t.id in (
select tp.task_id
from task_participant tp
where tp.person_id = p.id )
and p.id = 1234
...
- 111
1
vote
1
answer
585
views
Getting the "flat" query corresponding to a view using nested views?
I'm programming an application that queries SAP Business One databases, which are incredibly complex, riddled with cryptic table and column names left over from ancient times (including many ...
- 111
0
votes
2
answers
937
views
How to retrieve missing values of an id from a list?
have a data structure that is value/tag inside a table on Oracle sql
Lets say I have this structore
exTable
ID
TAG
VALUE
1
1
x
1
4
y
2
2
x
2
3
y
2
4
z
I want to retrieve the missing tags for a ...
1
vote
2
answers
2k
views
Mssql subquery inside a substring
I need to remove the last character of my subquery but I cannot figure out how to reference my subquery in the subselect as I need the length of it.
The subselect collects all rows and converts them ...
4
votes
1
answer
6k
views
Unexpected unique constraint violation during update
In table foo with two columns (id and seq), I'd like to add +1 to seq for all records with an arbitrary seq > 4738. The plan is to insert a new record at seq=4739 once all the seq > 4738 records ...
- 153
0
votes
3
answers
3k
views
I am trying to find the average of a COUNT request [closed]
I have completed the query below from an invoices table which gives me the total invoices attached to each house ID. So house_id 114 has 14 invoices attached to it. Is there a way to feed this result ...
- 13
0
votes
3
answers
71
views
Query to fine "Order_num" that has no two equal "product_name"
I have a table with below structure:
create table A_TEST
(
order_num int,
product_name varchar(50),
product_amount int,
product_price int
);
Sample data :
insert into a_test (...
- 1,486
8
votes
1
answer
6k
views
Why is SQL Server spliting it's (JSON) response into multiple rows?
I am trying to build a query that results in a JSON object generated by SQL Server. I've found that I can use subqueries to populate a field (in this case the questions field) with a JSON string that ...
- 311
-1
votes
1
answer
2k
views
Write a dynamic query of nested subqueries [duplicate]
Let's take an everyday query with a list of nested queries so I can show you the problem, see how we'd have to add a subquery when a new Certification comes out:
select DISTINCT e.display_name as &...
- 201
0
votes
1
answer
815
views
Sql Group by on subquery rows
I have this table :
ArretProductionJournee(Id, DateArret,HeureDebut,HeureFin,EnumArret)
Example :
DateArret ||HeureDebut ||HeureFin ||EnumArret
2020-11-30 ||2020-11-...
0
votes
1
answer
74
views
How to delete Specific row
http://sqlfiddle.com/#!9/df6245
How to delete all rows Where brand not equal to 107
for example in above fiddle GRPCD 44111 have 107 so it should not delete both rows which have GRPCD 44111 but it ...
- 23
2
votes
2
answers
36
views
How to filters data with two windows conditions?
I want to get the result of two sub-queries. In my case I have users, user can have multiple access for doors, but sometimes doors are disabled so the access exist but do not point to any doors. I ...
- 651
-4
votes
1
answer
778
views
Error when running query as a subquery [closed]
The inner query runs successfully alone, but when I run it as a subquery, an error pop up.
My query:
Select TOP 3 *
FROM (select
fr.rating
, sp.SpId
,sp.FName
,sp....
0
votes
2
answers
53
views
Optimize duplicated but negated subquery
I have what I call a 'Hamlet' query in the form:
select * from stuff a
left join otherStuff b on (a.x=b.x) -- lots of joins ommited
where
(b.ToBe = 1 and a.Being in (select Being from stuffThatIs ...
- 101
2
votes
1
answer
47
views
SQL daily value (today value - yesterday value)
I have a database where there are sales .
+----+-----------+------------+
| ID | Quantity | DATE |
+----+-----------+------------+
| 1 | 40 | 2020/10/10 |
| 2 | 0 | 2020/10/01 |...
- 23