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
1
vote
2 answers

Correlation over derived table in jOOQ

I have a simple parent - child (one to many related) tables. Here is a simplified example where I'm tracking trains and their locations. Table TRAIN - ID - NAME Table TRAIN_STATUS - ID - TRAIN_ID (fk) - STATION_CD -…
Keerthi
  • 466
  • 6
  • 12
1
vote
1 answer

MySQL Join Optimisation: Improving join type with derived tables and GROUP BY

I am trying to improve a query which does the following: For every job, add up all the costs, add up the invoiced amount, and calculate a profit/loss. The costs come from several different tables, e.g. purchaseorders, users_events (engineer…
Nicholas Shanks
  • 10,623
  • 4
  • 56
  • 80
1
vote
1 answer

How to remove duplicate records from a MySql 5.5. Derived Table, which aggregates data by date

The following table called hb_lead stores a unique lead, code and timestamp. Let's call the events recorded here reg id_hb_lead | lead_code | creation_date -----------|-----------|-------------- 1 | ABC | 2018-10-01 2 |…
digitai
  • 1,870
  • 2
  • 20
  • 37
1
vote
1 answer

Use ORDER BY in Derived table

I have 7 metric names and all these metrics should ideally be updated every month but sometimes that doesn't happen. In that case I'm required to carry forward my previous month metric value, red threshold and yellow threshold. All the data comes…
Aditya Chavan
  • 11
  • 1
1
vote
3 answers

How to Select top n Records for Each Category

I'm trying to write a query that joins 2 tables and will give me the top 5 names and the amount of items they sold at a location between a certain date range i.e. 01-01-2016 through 12-31-2017. From what I've been researching, This is what I came…
Bmorewize
  • 39
  • 8
1
vote
1 answer

Derived Table Error: "The multi-part identifier could not be bound"

I'm having trouble getting the results I would like from the query I've built. The overall goal I'm trying to accomplish is to get the first odometer reading of the month and the last odometer reading of the month for a specific vehicle. I would…
awilliams8976
  • 91
  • 9
1
vote
2 answers

mysql sum over derived table is faster than sum up without it

I cannot understand why the first query, which is using a derived table, is slower than the second one. My table: CREATE TABLE `test` ( `someid` binary(16) NOT NULL, `indexedcolumn1` int(11) NOT NULL, `indexedcolumn2` int(10) unsigned NOT…
holodoc
  • 77
  • 1
  • 11
1
vote
2 answers

MySQL DELETE Query with derived table

Good day all. I am stuck on a MySql delete query. Basically I need to delete the results of this SELECT query : SELECT radcheck.* FROM (SELECT permanent_users.realm, devices.name, devices.created, TimeDiff(Now(), devices.created) AS…
XeWonder
  • 39
  • 7
1
vote
1 answer

how to make mysql derived table use index

MySQL version 5.6.33 I have two tables (files & details) to inner join table files, has an index called idx_record(record_id) table details, has an index called idx_end_org_record(end_at, org_id, record_id) explain SELECT id as file_id, …
JOJO
  • 13
  • 1
  • 3
1
vote
1 answer

How to do outer join with inline view (select in from clause) in Postgresql

I have a query similar to this simplified example: select u.id, sq.score from usr as u, (select user_id, score FROM score WHERE bar = ?) as sq where u.id = sq.user_id I would like the join (u.id = sq.user_id) to be an outer join. I can't figure…
David Tinker
  • 9,383
  • 9
  • 66
  • 98
1
vote
1 answer

Please Explain how to Express this SQL as a Single Statement using Derived Tables

For my own edification, I'm trying to write this SQL functionality in a single statement without using temporary tables. For the life of me, I can't get the query to work without getting a MySQL "ERROR 1248 (42000): Every derived table must have…
Chris Betti
  • 2,721
  • 2
  • 27
  • 36
1
vote
1 answer

Update a table with conditions in specfic order

I have searched the web for my problem, tested some subqueries and derived table approaches with Case Statements, but didn´t get the result. Perhaps you can help? Thanks. The examples below are just an example. # generate the table as it is DROP…
Sven
  • 443
  • 2
  • 10
1
vote
1 answer

Subquery in select statement cant find derived table?

I am wondering if there is a way to make this work. I am deriving a table "WHERE lie_start='green'" (and a bunch of other conditions which i don't wanna repeat), need to get the number (and several other information) off it. Additionally I need the…
Deepy
  • 11
  • 1
1
vote
1 answer

How to use nested SELECT statements in sql?

I have two tables as Book +------+--------------+--------+----------+ | bkey | bname | nochap | b_id | +------+--------------+--------+----------+ | 1 | Let Us C | 17 | luc13 | | 2 | OOP with C++ | 17 | oopwcpp6…
Siraj Alam
  • 9,217
  • 9
  • 53
  • 65
1
vote
2 answers

Oracle sql derived table - optional aliasing

I came across a client's query yesterday, it is something like: select count(*) as countall, person, location from (select custid, min("Date") as theDate, person, data.location …
n8.
  • 1,732
  • 3
  • 16
  • 38
1 2 3
…
12 13