Stack Overflow
Stack Overflow

Questions tagged [derived-table]

A derived table is a term in SQL for a set of records that result from one query that can be used in another query. Derived tables are useful in simplifying complex queries into a series of simpler steps. They are often a simpler alternative to using temporary-tables.

A derived table is a term in SQL for a set of records that result from one query that can be used in another query. Derived tables are useful in simplifying complex queries into a series of simpler steps. They are often a simpler alternative to using temporary-tables.

188 questions
0
votes
1 answer

indexes and speeding up 'derived' queries

I've recently noticed that a query I have is running quite slowly, at almost 1 second per query. The query looks like this SELECT eventdate.id, eventdate.eid, eventdate.date, eventdate.time, eventdate.title, …
pedalpete
  • 21,076
  • 45
  • 128
  • 239
0
votes
1 answer

SQL Server reference fields in derived table with unions

I'm having a bit of an issue with some derived tables that I hope someone will be able to help with. What I've got is 2 derived tables inside a select statement that then uses a pivot to display the results horizontally rather than vertically. What…
denimknight
  • 301
  • 2
  • 9
  • 22
0
votes
1 answer

selecting from derived table in MySql

I have a table T like this: ID | column 1 | column 2 |...| column n Here ID is not a primary key. I performed some operation and group the results according to ID so I get a table T1 like this: ID | column 1 | column 2 |...| column x Now some…
Happy Mittal
  • 3,667
  • 12
  • 44
  • 60
0
votes
0 answers

SQL Server query that contains a derived table

I have created a select query that contains one regular table and two derived tables (subqueries). The derived tables are both joined with the regular table with left joins. The regular table contains over 7000 rows, but the query output is around…
user1964868
  • 3
  • 1
  • 4
0
votes
2 answers

Deriving and saving the historical values into a separate table, or calculate the historical values from the existing data only when they're needed?

tl;dr general question about handling database data and design: Is it ever acceptable/are there any downsides to derive data from other data at some point in time, and then store that derived data into a separate table in order to keep a history of…
CptSupermrkt
  • 6,844
  • 12
  • 56
  • 87
0
votes
3 answers

Is there some equivalent to subquery correlation when making a derived table?

I need to flatten out 2 rows in a vertical table (and then join to a third table) I generally do this by making a derived table for each field I need. There's only two fields, I figure this isn't that unreasonable. But I know that the rows I want…
stu
  • 8,461
  • 18
  • 74
  • 112
0
votes
1 answer

referring outer derived table from UPDATE command SQL

I need to refer to cartProduct derived table from query in UPDATE command. Can somebody explain me please what I doing wrong and how can I fix it. UPDATE memberships SET points = (points + COALESCE( (SELECT pts FROM ( SELECT pid0,…
s0ld13r
  • 775
  • 6
  • 9
0
votes
2 answers

Is there something equivalent to putting an order by clause in a derived table?

This is sybase 15. Here's my problem. I have 2 tables. t1.jobid t1.date ------------------------------ 1 1/1/2012 2 4/1/2012 3 2/1/2012 4 3/1/2012 t2.jobid …
stu
  • 8,461
  • 18
  • 74
  • 112
0
votes
1 answer

How to find column information for an aggregate grouping

I have a complicated query written on SQL Server 2000 which in part contains a join onto a derived table. This table is unfortunately not returning exactly how I desired as the underlying data differs to what I expected. Say the data are like…
Paul
  • 1,041
  • 11
  • 26
-1
votes
2 answers

Update missing values in MySQL 5.6 table by selecting values from closest records with same id

Let's say I have a table with logs. The value of status column may be NULL and I would like to UPDATE this value by copying value from the log for same settingid which have non-NULL status and closest (smaller or equal)…
vanishingESCkey
  • 19
  • 2
-1
votes
2 answers

MySQL - Derived table query not fetching data for nested Select query

I have a quey which has growth rates over a period of time. I am trying to obtain the overall growth between two rows that I specify. Here is the SQL fiddle http://sqlfiddle.com/#!9/1756ca/2 select i1.Month, i1.Rate, EXP(SUM(LOG(1+i2.Rate))) …
Quark
  • 49
  • 1
  • 14
-1
votes
1 answer

I am getting an Error on this even after adding an alias.... why?

SELECT * FROM (select flight_id, flight_no, scheduled_departure, scheduled_arrival, cast (scheduled_departure as text) TIMINGS from bookings.flights), as TABLE1 case when TIMINGS between 02:00:00+05:30 to 06:00:00+05:30 then 'Early morning…
jalalattar
  • 3
  • 2
-1
votes
1 answer

Error Code: 1305. FUNCTION sql_univeristy decode does not exist

I am getting an error while using this query in MySQL. Here is the query. select course_id, sec_id, ID, decode(name, NULL, '−', name) from (section natural left outer join teaches) natural left outer join instructor where semester='Spring' and…
Nisa
  • 7
  • 3
-1
votes
1 answer

How to select all rows with max values in a derived table?

I have the following table called "table3" +------+ | name | +------+ | aaa | | aaa | | aaa | | aaa | | aaa | | aaa | | bbb | | bbb | | bbb | | bbb | | ccc | | ccc | | ccc | | ccc | | ccc | | ccc | | ccc | | ccc | | ccc …
Kavindu Ravishka
  • 711
  • 4
  • 11
-1
votes
1 answer

count() function + derived table and Group - making it all work

Could someone please give me a hand in explaining in this example where the SUM function would go? I am trying to get a literal count on the number of returned columns, NOT the number of meta_key's for each post id. I want a number like there are 5…
Tim
  • 17
  • 6
1 2 3
…
12
13