SQLite - using subquery with LIMIT and error "table not found"

Question

I am trying to calculate the median for a time difference between 2 columns. I found the solution here BUT, when I incorporate it into my code, I get this error:

no such table: medianVal: SELECT timeToPost.

EX. of inner table (Users up):

id      userJoinDate    firstPost       timeToPost
---------------------------------------------------
3666    1/4/2015 10:48  1/4/2015 11:48    0.04
3669    1/13/2015 8:05  1/13/2015 9:05    0.04
3672    1/13/2015 8:27  1/13/2015 9:27    0.04
3675    1/13/2015 9:27  1/13/2015 10:27   0.04
3678    1/13/2015 11:02 1/13/2015 12:02   0.04

CODE:

SELECT timeToPost

FROM(
SELECT up.id, userJoinDate, firstPost, round(julianday(firstPost)-julianday(userJoinDate),2) as timeToPost
FROM (SELECT u.id, u.create_date as userJoinDate, min(p.create_date) as firstPost 
FROM Users u
JOIN posts p
ON p.user_id = u.id
WHERE u.create_date > '2015-01-01'
GROUP BY 1
) as up

ORDER BY 4 DESC
) as medianVal

LIMIT 2 - (SELECT COUNT(*) FROM medianVal) % 2 -- odd 1, even 2
      OFFSET (SELECT (COUNT(*) - 1) / 2 FROM medianVal)

score 1 · Answer 1 · answered Jul 21 '18 at 11:16

A table alias is different from a table itself. It cannot be re-used in a from clause.

However, a CTE can do what you want, so just rewrite the logic as:

with medianval as (
      select up.id, userJoinDate, firstPost, round(julianday(firstPost)-julianday(userJoinDate),2) as timeToPost
      from (select u.id, u.create_date as userJoinDate, min(p.create_date) as firstPost 
            from Users u join
                 posts p
                 on p.user_id = u.id
            where u.create_date > '2015-01-01'
            group by 1
           ) up
      )
select timeToPost
from medianval
order by timeToPost desc
limit 2 - (select count(*) from medianVal) % 2 -- odd 1, even 2
offset (select (count(*) - 1) / 2 from medianVal)

Note: This only addresses the missing table problem. I haven't validated the code for other problems or its suitability for calculating a median.

score 0 · Answer 2 · answered Jul 21 '18 at 19:36

0

Ahhh.... Thank you! I realized after the post that probably I could create a temporary table, but this even better!

answered Jul 21 '18 at 19:36

MC7836

84
8

SQLite - using subquery with LIMIT and error "table not found"

2 Answers2