CODEWITHSUNDEEP
sqlpostgresql
Ashish Chandran
Ashish Chandran

Reputation: 15

Multiple Queries as a Single Query

Two queries 

SELECT wavelength, reflectance 
FROM
  (SELECT 
      ROW_NUMBER() OVER(ORDER BY NULL) As RowNumber, * 
   FROM deodar) As foo 
WHERE (RowNumber % 5 = 0)

First query result will be taken as input for second query data for performing operations

SELECT 
   wavelength, 
   (LEAD(wavelength) OVER (ORDER BY wavelength) - wavelength) /
     (CASE WHEN (LEAD(reflectance) OVER (ORDER BY wavelength) - reflectance) = 0 THEN 1
           ELSE (LEAD(reflectance) OVER (ORDER BY wavelength) - reflectance) END)
      AS reflectance   
FROM  deodar

After performing operation, I want to show the result

Upvotes: 0

Views: 80

Answers (2)

Sarah G
Sarah G

Reputation: 810

The simple answer is that you can use a subselect.

SELECT 
   wavelength, 
   (LEAD(wavelength) OVER (ORDER BY wavelength) - wavelength) /
     (CASE WHEN (LEAD(reflectance) OVER (ORDER BY wavelength) - reflectance) = 0 THEN 1
           ELSE (LEAD(reflectance) OVER (ORDER BY wavelength) - reflectance) END)
      AS reflectance   
FROM (
   SELECT wavelength, reflectance 
   FROM
     (SELECT 
         ROW_NUMBER() OVER(ORDER BY NULL) As RowNumber, * 
      FROM deodar) As foo 
   WHERE (RowNumber % 5 = 0)
) bar

It's not clear if you want the LEAD operations to be applied before filtering or after. If you want them applied before filtering, you'd reverse the order of nesting.

For more complex operations, or for better readability/maintainability, PostgreSQL (and recent ANSI SQL) supports Common Table Expressions to let you create several virtual tables and select from them. The various virtual tables can reference each other—even recursively. Microsoft has some pretty good documentation.

Depending on the SQL engine you're using, CTEs may not perform identically to subselects. In the simple case where a virtual table is used only once, a subselect is likely to perform better (if there are any performance differences).

A CTE solution would look something like this:

WITH bar AS (
   SELECT wavelength, reflectance 
   FROM
     (SELECT 
         ROW_NUMBER() OVER(ORDER BY NULL) As RowNumber, * 
      FROM deodar) As foo 
   WHERE (RowNumber % 5 = 0)
 )
SELECT
   wavelength, 
   (LEAD(wavelength) OVER (ORDER BY wavelength) - wavelength) /
     (CASE WHEN (LEAD(reflectance) OVER (ORDER BY wavelength) - reflectance) = 0 THEN 1
           ELSE (LEAD(reflectance) OVER (ORDER BY wavelength) - reflectance) END)
      AS reflectance   
FROM bar

Upvotes: 1

Jonathan Hiben
Jonathan Hiben

Reputation: 553

SELECT wavelength,reflectance 
FROM
    (SELECT ROW_NUMBER() OVER(ORDER BY NULL) As RowNumber,* FROM deodar) As foo         
WHERE(RowNumber%5=0)

UNION

SELECT wavelength, 
   (LEAD(wavelength) OVER (ORDER BY wavelength) - wavelength)/
   (CASE WHEN (LEAD(reflectance) OVER (ORDER BY wavelength) - reflectance) = O THEN 1
    ELSE (LEAD(reflectance) OVER (ORDER BY wavelength) - reflectance) END)
   AS reflectance
FROM  deodar

The only condition to use UNION is that all the queries must return the same columns (types and names).

Upvotes: 0

Related Questions