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.
37 questions
11
votes
2
answers
15k
views
How to combine ORDER BY and LIMIT with an aggregate function?
Here is a fiddle for my question.
I have a simple table layout:
class
person: belongs to a class
I want to select all classes, and for each class, I want the first two person identifiers of the ...
- 455
5
votes
3
answers
6k
views
Query to find GUID's "not in" null containing subquery returns no results
When I have a query that checks if a column of type uniqueidentifer does not exist in a table that has a null value then I get no results back. If the subquery does not return a null it works fine and ...
- 203
49
votes
4
answers
69k
views
Using column alias in a WHERE clause doesn't work
Given a table users with two fields: id and email.
select id, email as electronic_mail
from (
select id, email
from users
) t
where electronic_mail = ''
Postgres complains that:
...
- 627
33
votes
1
answer
178k
views
Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used
I have two tables employee and phones. An employee can have 0 to n phone numbers. I want to list the employee names with their phone numbers. I am using the below query which runs fine.
SELECT ...
- 433
11
votes
2
answers
4k
views
Subquery gives no error for a non-existing column with the same name as in the outer query
I have two tables in a MySQL database - t1 with a column c1, and t2 with a column c2.
I run this query:
select * from t1 where c1 in (select c1 from t2);
The above query should give an error as c1 ...
- 197
4
votes
3
answers
439
views
Correlated Subquery in SQL Server 2014
I am running a correlated subquery to find out the listing of vendors (by Vendor Name) that are in different cities, states, i.e. we want to know the vendors that do not have a common city and state ...
- 875
3
votes
1
answer
34k
views
Delete from table where multiple fields match select subquery from other table
I want to delete an entry in a table where multiple fields match the results of another select subquery which takes data from another table.
This is what I have so far, though it doesn't work:
...
- 255
3
votes
1
answer
3k
views
Aliases with correlated subqueries
I am looking at an example of correlated subquery with aliases from a PostgreSQL book:
bpsimple=# SELECT * FROM orderinfo o,
bpsimple=# (SELECT * FROM customer c WHERE town = 'Bingham') c
bpsimple=# ...
- 1,879
2
votes
3
answers
4k
views
SQL Subquery , One to Multiple rows
Following query I have written returns the result as expected, example 121350 rows.
SELECT dbo.Articles_in_Consignment.Consignment_id,
dbo.ORDER_D.Orders_boxsize,
dbo.ORDER_D....
- 123
2
votes
2
answers
6k
views
Debit Credit Balance calculating from previous records with Pagination
I have a table which contains the following data:
ID In Out
1 100.00 0.00
2 10.00 0.00
3 0.00 70.00
4 5.00 0.00
5 0.00 60.00
...
- 123
35
votes
3
answers
6k
views
Why does this query work?
I have two tables, table_a (id, name) and table_b (id), let's say on Oracle 12c.
Why does this query not return an exception?
select * from table_a where name in (select name from table_b);
From ...
- 523
13
votes
1
answer
29k
views
Call function where argument is a (sub)select statement
My function takes an int4 as argument and returns a table:
SELECT * FROM test_function(545421); -- works fine
SELECT * FROM test_function(SELECT customerid
FROM tableX ...
- 245
8
votes
3
answers
13k
views
Performance of count(*) in subquery
Suppose we have the following queries:
1.
SELECT COUNT(*) FROM some_big_table WHERE some_col = 'some_val'
2.
SELECT COUNT(*) FROM ( SELECT * FROM some_big_table WHERE some_col = 'some_val' ...
- 195
6
votes
2
answers
2k
views
Is there a way to hint to query optimizer to MySQL which constraints should be done first?
This is my current query:
SELECT BusinessID as ID,
111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from
(
SELECT *
FROM
...
- 1,355
6
votes
3
answers
26k
views
MySQL: Delete all but last N records
Consider the following table:
mysql> DESCRIBE pixels;
+---------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default ...
- 11.9k
5
votes
1
answer
6k
views
Use NEW in FROM clause in Postgres trigger?
I am trying to write a Postgres trigger to unnest an array field before inserting or updating a new row. E.g.
SELECT
unnest(something)
FROM NEW
However, this seems to lead to an error:
relation '...
5
votes
2
answers
5k
views
How can i return the max of a sum by each team?
I have a lot of teams and i want to know the maximum of sum total of each team.
This's my query :
SELECT campaign_id,
campaign_identifier,
team,
campaign_name,
Month(...
- 53
5
votes
1
answer
5k
views
Why does adding ROWNUM to a query improve performance?
I have two queries:
1) This query has a ROWNUM column (takes 20 seconds to execute):
SELECT
ROWNUM
,ROAD_ID
,VERTEX_INDEX
,SDE.ST_X(ST_POINT) AS X
,SDE.ST_Y(ST_POINT) AS Y
...
- 1,497
4
votes
2
answers
5k
views
Why do I need to use a sub query to filter down a grouped select?
If I do this --
SELECT dv.Name
,MAX(hb.[DateEntered]) as DE
FROM
[Devices] as dv
INNER JOIN
[Heartbeats] as hb ON hb.DeviceID = dv.ID
WHERE DE < '2013-03-04'
GROUP BY dv.Name
...
- 1,707
4
votes
2
answers
15k
views
Use array expression from subquery in ANY() condition
I have a relatively complicated query with a subquery fetching an array like so:
...
ARRAY(SELECT category_id FROM category_schedule_con con
WHERE s.id = con.schedule_id ORDER BY category_id) ...
- 143
4
votes
1
answer
461
views
Concurrent update on two different tables fail
I'm using PostgreSQL with the following database schema:
CREATE TABLE plans (
slug VARCHAR(500) PRIMARY KEY
);
CREATE TABLE users (
id VARCHAR(16) PRIMARY KEY,
org_id VARCHAR(16) NOT NULL
...
- 143
3
votes
2
answers
2k
views
Find the second highest value from a subquery or derived table
I have a subquery which returns the following results.
'c003', '120000.00'
'c002', '90000.00'
'c001', '20000.00'
'c005', '8000.00'
'c004', '5000.00'
I want to find the second highest value from this....
- 153
3
votes
2
answers
2k
views
Join is causing other column data to change?
I'll lead off with saying that I am relatively new to databases and SQL and I am trying to make an addition to a View in SQL Server that a previous co-worker created.
I am trying to join the ...
- 133
3
votes
2
answers
3k
views
Optimize a query with two range conditions
I have a structure similar to this one:
CREATE TABLE `author` (
`id` int(11) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8 ...
- 131
3
votes
1
answer
3k
views
Best practice: Unions or a derived table?
I've inherited a medium-sized database with a terrible schema. The sanitized portion in question is like so:
CREATE TABLE `pending` (
...
`invoice` int(11) DEFAULT NULL,
`lid` int(11) DEFAULT ...
- 153
3
votes
1
answer
5k
views
User variable in nested subquery not getting picked up - in complex sort of 2 tables with gaps
I have just recently learned about user variables (ie, @myvar) and am trying to create a complex sorting query that takes 2 tables where the second is missing a column which then gets filled in by a ...
- 189
2
votes
1
answer
1k
views
Why Subquery do not filter the result in multiple Subquery
This is Query (skipped selection for simplicity and also it work perfect):
SELECT
*
FROM
(SELECT
a.striker
FROM
ball_by_ball a
LEFT JOIN batsman_scored b USING (...
- 101
2
votes
1
answer
2k
views
Deduplicate Data Using Having with Subquery
I've inherited a DB that was lacking a unique constraint so data was/is duplicated. I'm now trying to remove the duplicate records, then add a constraint to block this going forward.
I have this ...
- 105
2
votes
1
answer
3k
views
Updating a table with duplicate id's while avoiding " single-row subquery returns more than one row" [closed]
I'm currently making SQL that'll update data from 1 table, based on data from another table for my company
update table1
set column1 = (SELECT color
FROM table2
Where ...
- 47
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
2
votes
1
answer
5k
views
Using OFFSET in a large table by a simple sub-query
The slow performance for using OFFSET in large table has been widely discussed in various blogs, and the most efficient way is to use an INNER JOIN as
SELECT *
FROM table
INNER JOIN (
SELECT id
FROM ...
- 4,531
2
votes
1
answer
14k
views
How to select a subset from a query result
I have a query which calculates the Time Difference and Distance Between the Next and Previous Row:
SELECT * FROM (
SELECT
iVehicleMonitoringId AS PreviousId,
...
- 431
2
votes
2
answers
2k
views
Slow CREATE table from subquery using SELECT .. INNER JOIN
This query takes ~90 seconds:
CREATE TABLE filtered
(
SELECT DISTINCT
f2.cityCode,
f2.productCode,
f2.productDesc,
f2.inventoryDate,
f2.inventoryLevel
FROM
etm
...
- 123
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
1
answer
3k
views
Postgres Nested WHEN Aggregate Function
In PostgreSQL (version 9.4) I am trying to construct a query to determine in various tranches how many times various numbers appear in the data set between ranges. When I query group-ing by "...
- 115
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
0
votes
2
answers
8k
views
Performing query based on another query's result
First I have to run the query (1) using the pagedetailid number to get the adblockid. Then I have to put that adblockid into the other query (2) to get the result. What I would prefer is a single ...
