0

I am executing the following query on PHP server

SELECT vv.id, vv.feature_id, vv.val, vv.lab, vv.variable_id, vv.subset_id, us.ord, us.nam subset_nam, us.ord, us.color
FROM variable_values vv
  LEFT JOIN unit_subsets us ON vv.subset_id = us.id
  INNER JOIN variables v ON vv.variable_id = v.id
WHERE v.nam='LanguageDistribution'
      AND vv.feature_id IN (37,40...)

and it returns 65000 rowws, which depletes available memory.

Later I am programmatically grouping rows by vv.feature_id and retain only N largest values per each group.

Can I do this on SQL side?

When I write

SELECT vv.id, vv.feature_id, vv.val, vv.lab, vv.variable_id, vv.subset_id, us.ord, us.nam subset_nam, us.ord, us.color
FROM variable_values vv
  LEFT JOIN unit_subsets us ON vv.subset_id = us.id
  INNER JOIN variables v ON vv.variable_id = v.id
WHERE v.nam='LanguageDistribution' AND
    vv.id IN (SELECT vv2.id FROM variable_values vv2 WHERE vv2.variable_id = v.id ORDER BY vv2.val DESC LIMIT 5)
      AND vv.feature_id IN (37,40,63...

it swears

[SQLITE_ERROR] SQL error or missing database (no such column: v.id)

How to succeed?

Dims
  • 47,675
  • 117
  • 331
  • 600
  • 1
    Show the `.dump` of a suitable toy database. Use the sqlite commandline tool to create that. – Yunnosch May 22 '17 at 20:49
  • check https://stackoverflow.com/questions/751399/sql-query-how-to-apply-limit-within-group-by – Yunnosch May 22 '17 at 21:08
  • @Yunnosch in this example they refer outer query from inner one, which does not work for me. – Dims May 23 '17 at 08:20
  • Explain why and show the `.dump`of a suitable toy database. If you do not understand that, ask. – Yunnosch May 23 '17 at 17:19
  • @Yunnosch yes, I don't understand the purpose of this; is my SQL syntax correct and should work and we are debugging SQLITE? or my syntax is wrong and I should write something else? later I will make toy database of course and try to reproduce the situation – Dims May 23 '17 at 20:04
  • Make toy now. Show output of `.dump`. – Yunnosch May 23 '17 at 20:06
  • https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Yunnosch May 23 '17 at 20:10

0 Answers0