CODEWITHSUNDEEP
sqlpostgresqlany
Anonymous
Anonymous

Reputation: 4757

SQL query: how do results get retrieved via "any" in own columns?

Another SQL question. I have the following query:

SELECT EXTRACT(epoch from dt) as diff
from (
     SELECT time_col - lag(time_col) OVER dt
     FROM myTable where elementID=1234
 ) as dt

This calculates the time difference and I get e.g. the following result table for ElementID 1234:

34
345
6
2
...

However I want to do this for every element ID that is stored in the table elementTable. Here is my approach:

SELECT EXTRACT(epoch from dt) as diff
from (
     SELECT time_col - lag(time_col) OVER dt
     FROM myTable where elementID=any(select elementID from elementTable) 
 ) as dt

This is very close to the wanted result, however I get everything in a single column. E.g.

34  <- For element id = 1234
345 <- For element id = 1234
6   <- For element id = 1234
2   <- For element id = 1234
83  <- For element id = x
4   <- For element id = x
6   <- For element id = x
...

What I want to have is this (ordered in columns by element id):

 1234 | x | ...
 ------------------
 34     83  ...
 345    4   ...
 6      6   ...
 2

Sorry for bothering you with my SQL questions, I'm trying to learn...

Upvotes: 0

Views: 112

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657357

Use a simple JOIN instead of the convoluted ANY construct:

SELECT element_id
      ,EXTRACT(epoch from (time_col - lag(time_col) OVER (ORDER BY something)))
FROM   tbl
JOIN   elementTable USING (element_id);

Also, you broke the valid solution @Clodoaldo provided for your previous question: the OVER clause dt for the window function was undefined in your query.

To get the result you are asking for, look into the crosstab() function of the tablefunc module. More info here:
Sum by month and put months as columns

Dynamic number of columns

"Dynamic" makes this a lot harder, since SQL wants to know the resulting columns beforehand. I covered the topic comprehensively in this related answer:
Dynamic alternative to pivot with CASE and GROUP BY

If an array instead of individual columns is good enough for you, look at the answer to the same question by @Clodoaldo, or, for more explanation, the chapter "Variable number of columns sharing the same type" in this related answer:
Refactor a PL/pgSQL function to return the output of various SELECT queries

Upvotes: 2

Related Questions