Is there a way to reuse subqueries in the same query?

Question

See Update at end of question for solution thanks to marked answer!

I'd like to treat a subquery as if it were an actual table that can be reused in the same query. Here's the setup SQL:

create table mydb.mytable
(
    id integer not null,
    fieldvalue varchar(100),
    ts timestamp(6) not null
)
unique primary index (id, ts)

insert into mydb.mytable(0,'hello',current_timestamp - interval '1' minute);
insert into mydb.mytable(0,'hello',current_timestamp - interval '2' minute);
insert into mydb.mytable(0,'hello there',current_timestamp - interval '3' minute);
insert into mydb.mytable(0,'hello there, sir',current_timestamp - interval '4' minute);
insert into mydb.mytable(0,'hello there, sir',current_timestamp - interval '5' minute);
insert into mydb.mytable(0,'hello there, sir.  how are you?',current_timestamp - interval '6' minute);

insert into mydb.mytable(1,'what up',current_timestamp - interval '1' minute);
insert into mydb.mytable(1,'what up',current_timestamp - interval '2' minute);
insert into mydb.mytable(1,'what up, mr man?',current_timestamp - interval '3' minute);
insert into mydb.mytable(1,'what up, duder?',current_timestamp - interval '4' minute);
insert into mydb.mytable(1,'what up, duder?',current_timestamp - interval '5' minute);
insert into mydb.mytable(1,'what up, duder?',current_timestamp - interval '6' minute);

What I want to do is return only rows where FieldValue differs from the previous row. This SQL does just that:

locking row for access
select id, fieldvalue, ts from 
(
    --locking row for access
    select
        id, fieldvalue, 
        min(fieldvalue) over 
        (
            partition by id 
            order by ts, fieldvalue rows 
            between 1 preceding and 1 preceding
        ) fieldvalue2,
        ts
    from mydb.mytable
) x
where
    hashrow(fieldvalue) <> hashrow(fieldvalue2)
order by id, ts desc

It returns:

+----+---------------------------------+----------------------------+
| id |           fieldvalue            |             ts             |
+----+---------------------------------+----------------------------+
|  0 | hello                           | 2015-05-06 10:13:34.160000 |
|  0 | hello there                     | 2015-05-06 10:12:34.350000 |
|  0 | hello there, sir                | 2015-05-06 10:10:34.750000 |
|  0 | hello there, sir.  how are you? | 2015-05-06 10:09:34.970000 |
|  1 | what up                         | 2015-05-06 10:13:35.470000 |
|  1 | what up, mr man?                | 2015-05-06 10:12:35.690000 |
|  1 | what up, duder?                 | 2015-05-06 10:09:36.240000 |
+----+---------------------------------+----------------------------+

The next step is to return only the last row per ID. If I were to use this SQL to write the previous SELECT to a table...

create table mydb.reusetest as (above sql) with data;

...I could then do this do get the last row per ID:

locking row for access
select t1.* from mydb.reusetest t1,
(
    select id, max(ts) ts from mydb.reusetest
    group by id
) t2
where
    t2.id = t1.id and
    t2.ts = t1.ts
order by t1.id

It would return this:

+----+------------+----------------------------+
| id | fieldvalue |             ts             |
+----+------------+----------------------------+
|  0 | hello      | 2015-05-06 10:13:34.160000 |
|  1 | what up    | 2015-05-06 10:13:35.470000 |
+----+------------+----------------------------+

If I could reuse the subquery in my initial SELECT, I could achieve the same results. I could copy/paste the entire query SQL into another subquery to create a derived table, but this would just mean I'd need to change the SQL in two places if I ever needed to modify it.

Update

Thanks to Kristján, I was able to implement the WITH clause into my SQL like this for perfect results:

locking row for access
with items (id, fieldvalue, ts) as
(
    select id, fieldvalue, ts from 
    (
        select
            id, fieldvalue, 
            min(fieldvalue) over 
            (
                partition by id
                order by ts, fieldvalue
                rows between 1 preceding and 1 preceding
            ) fieldvalue2,
            ts
        from mydb.mytable
    ) x
    where
        hashrow(fieldvalue) <> hashrow(fieldvalue2)
)
select t1.* from items t1,
(
    select id, max(ts) ts from items
    group by id
) t2
where
    t2.id = t1.id and
    t2.ts = t1.ts
order by t1.id

Answer 1

Does WITH help? That lets you define a result set you can use multiple times in the SELECT.

From their example:

WITH orderable_items (product_id, quantity) AS
 ( SELECT stocked.product_id, stocked.quantity
   FROM stocked, product
   WHERE stocked.product_id = product.product_id
   AND   product.on_hand > 5
 )

SELECT product_id, quantity
FROM orderable_items
WHERE quantity < 10;