Selecting duplicate records except the first and last duplicate with Select by expression in QGIS

Question

I'd like to know how to select duplicate records except the first and last one, with the "Select by attribute/expression". On GIS SE I found a solution for selecting duplicates except the first one, with the below expression (on the picture is the current solution I have that selects all but the first duplicate):

CASE
    WHEN "PortClustersIDs3_CLUSTER_ID" IS NOT NULL
        THEN array_contains(
                array_remove_at(
                    array_agg($id, "PortClustersIDs3_CLUSTER_ID" ),
                    0),
                $id)
END

I would like to modify the above expression or write up a completely new expression for selecting all but the first as well as the last duplicate. So in the below picture/example I don't want the 20th row to be selected and the 17th row already isn't.

---

After @MrXsquared’s answer, I still have a problem with my specific case. I need to use their expression inside a CASE statement (or maybe if statement?), because I need to filter out rows when the "PortClustersIDs3_CLUSTER_ID" feature is not NULL.

As you can see from the pictures I now added it works on the first occurrence of duplicates (see 2nd screenshot) but when a few rows after the "PortClustersIDs3_CLUSTER_ID" IS NOT NULL again the code provided does not work (see 3rd screenshot). I think it's a problem with my CASE statement because you probably should be building new array for each set of rows where "PortClustersIDs3_CLUSTER_ID" is not null?

Answer 1

You can use this with select by expression:

with_variable('arr',array_agg($id, "myfield"),
if($id in (@arr[0],@arr[-1]),false,true))

Or for older QGIS versions where index brackets are not supported and without the use of a variable its:

if($id in (array_first(array_agg($id, "myfield")),array_last(array_agg($id, "myfield"))),false,true)

Example result: