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
1
vote
2
answers
339
views
Referencing a SELECT subquery value in the WHERE clause
Complete query
SET @vSelect =
'
SELECT
T1.BranchShortName AS BranchShortName,
(
SELECT TT1.EmployeeName
FROM [dbo].[...
- 15
1
vote
1
answer
1k
views
Selecting data from multiple rows and insert it into a column
My table is like this:
temp1
name city phone_no pincode
-----------------------------------
amit Delhi 12345 123
Rajiv Bombay 836536 432
How do I transform the last ...
- 11
1
vote
2
answers
621
views
optimization of expensive subquery
The following query gets the last report (where latitude and longitude and secs are <> 0) associated with the specified units:
SELECT
reports.*
FROM
reports
INNER JOIN
...
- 1,879
1
vote
3
answers
4k
views
How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql
I have legacy tables similar to the following:
employee
------------------------------------
| employee_id | name
------------------------------------
| 1 | David
| 2 ...
- 111
1
vote
2
answers
1k
views
How to use GROUP BY in an efficient way without losing attributes? [closed]
I am working with PostgreSQL.
My idea is to group players who appear more than 3 times in table-a.
table-a = <player_id, year_world, c, d, e>
With the first subquery I get player_id and the ...
- 109
1
vote
1
answer
453
views
Logic of Assigning values to variables in Scalar-valued Functions
I am currently working on updating old scalar valued functions with the hope of making them more efficient or changing them into Inline TVFs.
While troubleshooting my rewrites I came across some ...
- 113
1
vote
1
answer
38
views
Very weird result of CTEs and subqueries [closed]
WITH t1 AS (SELECT a.id,
AVG(standard_qty) std_avg,
AVG(poster_qty) pos_avg,
AVG(glossy_qty) gloss_avg
FROM accounts a
...
- 13
1
vote
2
answers
1k
views
Does the EXIST clause only return distinct rows by its nature?
I have two queries that appear to be logically equivalent, yet they return different result sets. I am using the AdventureWorks2012 database.
The first query uses an EXISTS clause with a subquery:
...
- 311
1
vote
2
answers
9k
views
How to access row value from UPDATE sub query function?
I would need to use UPDATE like this:
update offer set (bank, bank_id) = (cost, gid)
from (
select cost, osm_points.gid from pgr_kdijkstraCost(
'SELECT gid as id, source, target, walk_cost as cost ...
- 11
1
vote
3
answers
12k
views
Joining two tables by the nearest match
General problem: I want to do a left join on two huge tables, but there is no matching key - I want to join the left table with the "nearest" row in the right table. In this example I want to join ...
- 211
1
vote
1
answer
378
views
How to make this nested query :-
I've a table called foo which stores the child and parent information of some entity called bar, with level information as well. If there is no parent of bar, then in the parent_id column it will ...
- 115
1
vote
2
answers
398
views
Can some redundancy in this nested SELECT COUNT query be eliminated?
For the record, this is not a homework question. I am a professional brushing up on my SQL skills.
Given a table called world that looks like this,
I want to find the continents where all countries ...
- 175
1
vote
2
answers
408
views
PostgreSQL: windowing to replace multiple sub-queries ... hopefully
I have two tables:
CREATE TABLE fund_data
(
id serial NOT NULL,
fund_entries_id integer NOT NULL,
fund_val numeric(25,6) NOT NULL,
bbg_pulls_id integer NOT NULL,
CONSTRAINT fund_data_pkey ...
1
vote
1
answer
28k
views
Correlated sub-query on the same table with only one alias
I have query which look like this
SELECT *
FROM my_table
WHERE ... AND id IN (SELECT t2.id
FROM my_table t2
WHERE id = t2.id AND ... );
I know in this ...
- 13
1
vote
1
answer
4k
views
Sub-Query group-by count(*) not returning 0
Prefix: I am working on an application that is tracking employee training time and I need to get the total number of employees that have reached 75 total hours of training. After researching for some ...
- 99
1
vote
2
answers
687
views
how do I use a value from the select in a joined subquery?
I'm trying to avoid a stored procedure or bringing it into code but they may be my only alternatives. Here's my sql. Does anyone know any SQL voodoo that could make this happen without a stored ...
- 93
1
vote
2
answers
2k
views
How do I change all Oracle profiles PASSWORD_LIFE_TIME to UNLIMITED?
This query:
select distinct profile from dba_profiles;
Returns 3 rows:
default
default1
default2.
I want to change the PASSWORD_LIFE_TIME for all these profiles to UNLIMITED with a single query or ...
- 193
1
vote
2
answers
405
views
Effect of where statement in subquery (to get rid of unnecessary join)
Please consider the following query:
select *
from parent p
where p.id in (
select p.id
from parent p
inner join child c on c.parentId = p.id
where p.age > 50
)
Is it safe (in general!) ...
- 111
1
vote
2
answers
4k
views
How to optimise MySQL query that uses dependent subquery
I have a web app that has a custom feed of images. The image queried are based on whether the users have previously seen the image and ordered by a Hacker News style score.
There are only 1000 rows ...
- 111
1
vote
1
answer
320
views
Query for repeating sequences in SQLite
I have an SQLite DB, let's assume the id can be considered as the order of rows, I want to find repeating sequences in the table according to certain criteria.
consider this example (http://sqlfiddle....
- 167
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
3
answers
4k
views
Use an array returned from a subquery as argument in WHERE clause with ANY function in outer query
A query like this:
SELECT *
FROM person
WHERE person.favorite_number = ANY(
ARRAY[ 2 , 3 ]
)
;
…runs. The ANY() function tells us if the single value is found within an array of values. So, ...
- 11k
1
vote
1
answer
265
views
conversion of a non correlated sub query to a join statement
Is it possible to get rid of the sub query and convert the entire query into a JOIN query?
SELECT student_id,
COUNT(attendance)
FROM student_attendance
WHERE attendance = 'ABSENT'
GROUP BY ...
- 257
1
vote
1
answer
4k
views
How does a subquery use the main query columns?
I'm really confused by this SQL here, it's used in our app but I have no idea how it works. I put it in from examples I found online and it seems to have no issues.
What it's doing is grabbing the ...
- 33
1
vote
2
answers
156
views
Possible to rewrite this query as a join?
I'm fairly certain it's possible to rewrite this as a join query but I'm just not sure how to approach it:
SELECT
events.*,
(SELECT MAX(updated_at)
FROM events e
WHERE e.user_id =...
- 113
1
vote
2
answers
70
views
How can I use all the selected values of a column, one by one, in another query?
Use column values returned from the statement below:
SELECT DISTINCT(column_name) FROM Table1;
into the following query:
SELECT dif_col_1,
dif_col_2
FROM Table2
WHERE key_in_table = (...
- 11
1
vote
2
answers
398
views
How to use the output of one query in other
I am using following query to get the output as JobId, JobName and Start Execution date
SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ja.last_executed_step_id
FROM msdb.dbo....
- 190
1
vote
2
answers
129
views
How to sort records before inter-row calculation?
I have a time sequence data set to analyze but the difficulty is that
the data set is very big.
the time sequence events are committed by different objects with affiliation.
its in mysql, there is no ...
- 169
1
vote
1
answer
555
views
Using a subquery in place of a SELECT fieldname ...or how to achieve the same result?
My goal is to get a collection of ID's from Table 1 based on a query, a collection of ID's from Table 2 based on a query, and insert a new record into Table 3 for each permutation.
Basically, this:
...
- 301
1
vote
1
answer
6k
views
Match multiple components of a subquery CTE in PostgreSQL
I've got an app that grants access to users in varying different ways, and I'm attempting to write a query that determines access in all the different ways, but is readable, by breaking the access out ...
- 13
1
vote
2
answers
156
views
How do I add an alias to the table
SELECT MAX(COUNT), A.id FROM
(
SELECT supplier_id as id, COUNT(*) AS count
FROM `library`.`lms_book_details`
GROUP BY supplier_id
) GROUP BY A.id;
While executing the query, MySQL gives ...
- 153
1
vote
2
answers
162
views
How to restructure this slow query
For the following query:
SELECT l.*
FROM `leads` AS `l`
INNER JOIN `users` AS `u` ON l.client_id = u.id
WHERE (`l`.`lender_id` = 1)
AND (l.claimed_date IS NULL)
AND (`l`.`disabled` = 0)
AND (u....
- 510
1
vote
2
answers
695
views
Get the most recently completed step for each SQL job
I'm trying to get a list of jobs and their most recently completed step from the MSDB database. For a single job, this is pretty straight forward, something like:
SELECT TOP 1 j.job_id, j.name, h....
- 10k
1
vote
2
answers
93
views
How do I optimize this query that gets the newest entry of each group?
I have a query, that finds the newest item of each group based on the ID returned by a subquery. The problem is, that the table has a bit over 5000 entries and even with only this amount of entries ...
- 13
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
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
1
vote
1
answer
8k
views
Subqueries for JSON path where outer query is part of a UNION result in strings rather than JSON
Bear with me - the example code has bit going on and I'll do my best to explain it, below.
with ENTITIES as (
select [name] as entityName from (values ('A'), ('B'), ('C')) X([name])
),
PROPERTIES as (
...
- 1,645
1
vote
2
answers
278
views
Getting a query to work on SQLite as well as on MySQL
I have a subquery with a condition in the main query where clause.
Here is the sample schema
CREATE TABLE `payments` (
`id` int(11) NOT NULL,
`discount_id` int(11),
`amount` int(11)
);
...
1
vote
1
answer
1k
views
postgres subquery uses ungrouped column
I have a query in which I am trying to add a column which contains a percentage of records which have a certain value versus the total number of records. I can get the subquery to work on its own, but ...
- 115
1
vote
2
answers
616
views
How to get sum of 3 sub count queries and group them using an attribute
I have 3 queries as follows:
For the date 2019-12-02, I am calculating 3 type of values. The total value for 2019-12-02 must be the sum of these 3 queries.
Q1
SELECT count(*)
FROM ...
1
vote
1
answer
1k
views
Is there a way to alias columns for reference in subqueries?
I have a SQL statement in PostgreSQL that takes the following form:
SELECT * FROM db WHERE (b + 1) IN (SELECT b FROM db WHERE a = 7)
This query returns each record whose b value, incremented by 1, ...
- 133
1
vote
1
answer
516
views
Update "oldest" row
I need to update the "oldest" row in the database (for now, its limit 1, but I need to also be able to set it to n).
I'm essentially doing a constant stream of "update the oldest rows and retrieve ...
- 223
1
vote
1
answer
120
views
Mysql subquery not recognizing the subquery join table field
I have a three tables. The main table is the journeydata and next table is the alarm tables. The primary key for journeydata is journeydataID and its available as key in the alarm tables. Next in the ...
- 227
1
vote
1
answer
4k
views
Count up number of rows matching criteria in same table
I'm trying to extract a list of results, with the greatet number of rows found. Basically, we have a table of values, linked with the key link_id_fk. What I want to basically do is look at each ...
- 113
1
vote
3
answers
174
views
Slow query with sub SELECTs
Unsure how to optimize this query but it and about 15 variations of it need to run for a dashboard page. Any and all suggestions are welcomed! Thanks.
Time: 0.466s
Time without sub-selects: 0.058s
...
- 11
1
vote
1
answer
1k
views
MySQL subquery with IN operator optimization
I have a MySQL query in which I try to get articles with keywords matching those of a given page and that are not already posted to it.
The db tables are :
items : Articles table.
features : ...
- 35
1
vote
2
answers
22k
views
Using results from one query as the parameters for another
Is there a way to have a SQL query automatically update its' parameters based on the results of a previous query?
Here's the scenario I'm trying to achieve:
A report should automatically be run ...
- 451
1
vote
1
answer
7k
views
Reuse subquery as in another result
In a typical social-network-like application I have the following query that gathers news from a set of followed channels.
The main query gets the news, while the subquery gets the followed channels:
...
- 113
1
vote
2
answers
124
views
Is there a way to do the following query with joins instead of a sub query?
I am using SQL Server 2014 and I want to eliminate having to use the following sub query so I can create an indexed view.
The only thing similar to my problem is the greatest-n-per-group queries but ...
- 13
1
vote
2
answers
1k
views
simple SELECT EXISTS takes about two hours to execute
I have mysql server hosted. Now, If I issue simple SQL statement
SELECT EXISTS (SELECT * FROM ORDERS WHERE USER_ID='c85bc9f7-704f-418e-98af-a1528622dd42' AND PLACE_ID='2' AND PRODUCT_ID='2bc7277f-c7c3-...
- 127