If filter 110M
is exactly a subset of filter 126M
, then appending more AND
with WHERE
would have done the job.
$sql1 = "SELECT ..... WHERE ...";
$sql2 = $sql1 . "AND column-name = ....";
$sql3 = $sql2 . "AND column-name = ....";
If that is complex to accomplish try to CREATE VIEW of the previous SELECT statement and the next SELECT statement should query from the view.
Your query is needlessly complicated and can be untangled to this 100 % equivalent one:
SELECT gid, id, ST_MakeValid(geom) AS geom
FROM schema.table_polygons
WHERE geom IS NOT NULL;
Neither original nor this one remove any duplicates (except by possibly removing some with geom IS NULL
, but I doubt that was your intention).
Possible misunderstanding
There is a common misunderstanding concerning output column names in the WHERE
clause - where you can only reference input column names. So, geom
in:
WHERE geom IN ...
references the original input column not the output column (the result of ST_MakeValid(geom)
).
Related:
If your actual intention was to only keep rows where the result of ST_MakeValid(geom)
is equal to any currently existing geom
entry:
SELECT gid, id, ST_MakeValid(t.geom) AS geom
FROM schema.table_polygons t
WHERE EXISTS (
SELECT 1
FROM schema.table_polygons
WHERE geom = ST_MakeValid(t.geom)
);
I added a table alias and table qualification to clarify what's what.
The index you mention is only going to help with this variant.
If your actual intention was to merge duplicate rows after fixing geom
values:
SELECT DISTINCT ON (ST_MakeValid(t.geom))
gid, id, ST_MakeValid(geom) AS geom
FROM schema.table_polygons t
ORDER BY ST_MakeValid(t.geom), gid, id;
You did not define how to break ties and which row to keep from a set of dupes. I keep the row with the smallest gid
from each set. And the smallest id
from remaining dupes. Details:
Aside: Calling a schema "schema" is like naming your son "son" and hoping for the best that no more sons shall arrive. In other words: don't.
Best Answer
I'd actually think about adding a status column to the table tblstockingorders and having a trigger on that table that injects the status into the tblstatushistory.
Granted you'd have to do a 1-time update to all the rows in tblstockingorders (something similar to your query above) and set their last status, but this would give you best overall performance as these two tables grow.