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
3 answers

SQL syntax error: near "("

When I try to run this query: select branch_no, max (avg_salary) from (select allocatedto, avg (salary) from staff, worker where staff.staff_no = worker.staff_no group by allocatedto) as branch_avg (branch_no,…
Rohit N.
  • 27
  • 4
  • 7
1
vote
2 answers

Using the derived table in the JOIN statement

I have a database query in PHP similar to: SELECT * FROM ($myQuery) AS myDerivedTable JOIN ( SELECT COUNT(*) FROM myDerivedTable WHERE criteriaA ) AS A JOIN ( SELECT COUNT(*) FROM myDerivedTable WHERE criteriaB ) AS B JOIN ( SELECT COUNT(*) FROM…
user5222086
1
vote
1 answer

Teradata redistributing without a volatile table

I am stuck in a situation , where an impromptu report cannot be improved because IBM cognos will not support any kind of DDL including Volatile table creation in Teradata. The report query aggregations and in lists with some 5000 values . I know…
user1874594
  • 2,277
  • 1
  • 25
  • 49
1
vote
2 answers

mysql left join subquery with limit gives fields with null values on parent select

I have a query with joins and subquery (derived table). If I run it without LIMIT 1 the result will contain the vat and the id field with the proper values. The first query: SELECT i.id, i.vat, pl.invoice_id as inv_id, pl.product_id as pl_id,…
Tamas
  • 33
  • 5
1
vote
1 answer

Conversion failed in derived table used in vb.net table adapter

I'm having a problem when trying to insert parameters into a derived table that is part of my table adapter fill query in a vb.net application. I'm getting the "Conversion failed when converting date and/or time from character string" when I try to…
Andrew Gendre
  • 11
  • 2
1
vote
1 answer

SQL how to count the number of credit cards that had x transactions per month

I have a MySQL data set of credit card transactions: create table trans ( transdate date, card_id int ); I desire to know: 1. how many cards were used to make at least 1 transaction, per month 2. how many cards were used to make at least 5…
davidjhp
  • 7,816
  • 9
  • 36
  • 56
1
vote
2 answers

SQL how to count the number of credit cards that had at least 1,5,10,20 etc transactions in rows

I have a MySQL data set of credit card transactions. create table trans ( card_id int, amount int ); insert into trans values (1, 1); insert into trans values (2, 1); insert into trans values (3, 1); insert into trans values (4, 1); insert…
davidjhp
  • 7,816
  • 9
  • 36
  • 56
1
vote
2 answers

SQL Server: join on derived table that contains WITH clause?

I'd like to join on a subquery / derived table that contains a WITH clause (the WITH clause is necessary to filter on ROW_NUMBER() = 1). In Teradata something similar would work fine, but Teradata uses QUALIFY ROW_NUMBER() = 1 instead of a WITH…
We Are All Monica
  • 13,000
  • 8
  • 46
  • 72
1
vote
1 answer

LLBLgen 3.5 Derived Tables Query

I'm trying to get the following SQL to work with llblgen... SELECT *,(SELECT TOP (1) Id FROM Content.Grades WHERE Account = Authentication.Account.Id ORDER BY Grades.GradingDate DESC) AS CurrentGrade FROM Authentication.Account WHERE (SELECT TOP…
Reaper
  • 89
  • 6
1
vote
2 answers

Unknown column from within double nested derived table in JOIN statement

In my DB, there are two types of images: challenges, and answers. They both have lat/lng location columns. In this query, I wish to select the Challenge or Answer that the user created most recently via an INNER JOIN. (The business logic behind…
Matthew Housser
  • 1,012
  • 9
  • 21
1
vote
2 answers

How to combine variable assignment with data-retrieval operations in T-SQL

Just to clarify, I'm running Sybase 12.5.3, but I am lead to believe that this holds true for SQL Server 2005 too. Basically, I'm trying to write a query that looks a little like this, I've simplified it as much as possible to highlight the…
ninesided
  • 23,085
  • 14
  • 83
  • 107
1
vote
2 answers

CTE top - order by clause is not always working

I have the following T-SQL statement: ;WITH DataSource ([ColumnA]) AS ( SELECT TOP 100 [ColumnA] FROM [dbo].[TEST] WHERE [ColumnB] = 40 ORDER BY [ColumnC] DESC ) SELECT [ColumnA] ,COUNT([ColumnA]) FROM DataSource GROUP BY…
gotqn
  • 42,737
  • 46
  • 157
  • 243
1
vote
1 answer

How to use a table alias with a Union statement in Access?

In an unpivoting operation, I would like the following: SELECT A, B, C FROM [complex joins/where clause] As DerivedTable UNION SELECT A, B, D FROM DerivedTable UNION SELECT A, B, E FROM DerivedTable ... but it complains that DerivedTable cannot be…
mchen
  • 9,808
  • 17
  • 72
  • 125
1
vote
9 answers

How to find out 2nd highest salary of employees?

Created table named geosalary with columns name, id, and salary: name id salary patrik 2 1000 frank 2 2000 chinmon 3 1300 paddy 3 1700 I tried this below code to find 2nd highest salary: SELECT salary FROM (SELECT salary,…
franky
  • 23
  • 1
  • 1
  • 3
1
vote
1 answer

How can I filter with a derived table in MS SQL Server?

SQL Server Management Studio 2005 I wrote a query that would allow the user to search on various categories such as Description, Country of Origin, Brand Name, and Grow Method. The user is able to input data for as many filters as they would like…
davszur
  • 45
  • 1
  • 1
  • 5
1 2 3
…
12 13