Questions tagged [derived-tables]
The derived-tables tag has no usage guidance.
15
questions
1
vote
1
answer
76
views
Is it possible to reduce the duplication in this simple query?
I'm new to SQL and so this is probably a stupid question but can I reduce the duplication in this query? My actual query involves more complex expressions. Right now I'm just doing string ...
- 13
0
votes
1
answer
328
views
MySQL query - Passing variable into subquery's derived table
Is there a way to pass a variable into a subquery, and use that variable in a derived table in the subquery?
I am trying identify "active" organizations, and there are multiple actions that ...
- 101
0
votes
2
answers
220
views
Order by is ignored if inside a derived table of a update (only in MYSQL 5.7)
I have a update to fix positions values inside a table. The update worked fine in MYSQL 5.5, but after upgrade to MYSQL 5.7 it doesnt work anymore. Now, the "order by" inside the derived table is ...
0
votes
1
answer
52
views
mysql query with 1 derived table and 1 inner join takes forever to load
I tried removing some chunks of code little by little and found out that the condition inside the inner join is the devil. Can someone enlighten me on how to achieve this when I have like 10,000+ rows ...
1
vote
3
answers
249
views
Improve Query Performance of Select Statment inside IN Statment
My query is:
(3) UPDATE wp_postmeta set meta_value = 'outofstock' where meta_key = '_stock_status' and
post_id in
(
(2)SELECT post_id FROM
(
(1) ...
- 121
0
votes
1
answer
917
views
SQL query does not recognise derived table
So I've made a simple SQL query:
SELECT companyname
FROM works w1, (
SELECT companyname,sum(salary) AS sumsal
FROM works
GROUP BY companyname
...
- 3
3
votes
1
answer
197
views
Delete Joined to Derived Table Causes Index Scan and Table Spool
Our application uses a popular 3rd-party extension to Entity Framework that allows for valuable things that native Entity Framework is not capable of or designed to do, like bulk deletes and updates.
...
- 1,169
13
votes
4
answers
5k
views
Is it REALLY possible that the order will not be guaranteed for this particular redundant derived table?
I stumbled upon this question on a Twitter conversation with Lukas Eder.
Although the correct behavior would be to apply the ORDER BY clause on the outermost query, because, here, we are not using ...
- 897
9
votes
5
answers
31k
views
Difference between inline view and WITH clause?
Inline views allow you to select from a subquery as if it were a different table:
SELECT
*
FROM /* Selecting from a query instead of table */
(
SELECT
c1
FROM
...
- 3,107
0
votes
2
answers
728
views
Update statement using Case statement, derived table, and ROW_NUMBER
Running MS SQL 2008 R2. I am provided a CSV list of product-IDs which I parse into individual rows and normalize. I then need to take those product-IDs (anywhere from 1-10) and write them to their ...
- 3
2
votes
1
answer
120
views
mysql group by on table from two derived non-indexable
Working with mysql 5.7. I've got a query that ends up joining two derived tables and grouping on one of the columns. The query without the grouping runs pretty fast.... .5 seconds and returns 15K ...
- 149
2
votes
0
answers
411
views
How does the use of derived tables speed up sorting?
In the article 45 Ways To Speed Up Firebird Database, it states that
Use derived tables to optimize SELECT with ORDER BY/GROUP BY
Another way to optimize SQL query with sorting is to use ...
- 121
1
vote
0
answers
71
views
Get unique rows based on max of a column in mysql
I came across many solutions like the one in this question. However, they all are restricted to data from one table. I'm very very new to SQL. I'd like to get the same results as in the linked ...
- 111
0
votes
2
answers
339
views
How are derived table implemented in MySQL
When MySQL makes a derived table as a result of
SELECT (SELECT ...) etc
or SELECT * FROM a JOIN (SELECT * from B) etc
or SELECT * FROM (SELECT ...)
Are these derived temporary tables created in-...
- 983
8
votes
2
answers
22k
views
Are there advantages to using temporary tables over derived tables in SQL Server?
I read the derived tables have better performance than temporary tables, but anyway many SQL Server developers prefer the second ones. Why?
I must do queries with large data (millions records) and I ...
- 241