AOL Nathon AOL Nathon - 1 year ago 61
SQL Question

I want max volume of each datesection but when I run it show

"public".steponesection."Trade Price",
max ("public".steponesection."Trade Volume") OVER (partition by "public".steponesection.datesection) as max_Volm

FROM "public".steponesection)t

WHERE "public".steponesection."Trade Volume" = max_Volm

Results in this error:

[Err] ERROR: missing FROM-clause entry for table "steponesection"
LINE 10: WHERE "public".steponesection."Trade Volume" = max_Volm


Answer Source

The error message is pretty clear: the where clause cannot reference steponesection as on that level of the query there is no identifier with that name. The derived table is called t and that's what you need to use.

You also did not select the "Trade Volume" in the inner query, so it's not available in the outer level:

   SELECT "public".steponesection.datesection,
          "public".steponesection."Trade Price",
          "public".steponesection."Trade Volume",  --<< missing
           max ("public".steponesection."Trade Volume") OVER (partition by "public".steponesection.datesection) as max_volm
   FROM "public".steponesection
) t
WHERE t."Trade Volume" = t.max_volm --<< use the alias of the derived table

I would strongly recommend to avoid quoted identifiers like "Trade Price". They are much more trouble in the long run then they are worth

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download