SQL: SELECT rows with max date from created table

I´m wondering how to create a query that returns SERIALID, QTY, but only for the last INVOICEDATE.

To combine information from two tables I use UNION.

SELECT ab.SERIALID, ab.QTY, ab.INVOICEDATE
FROM (SELECT a.INVOICEDATE, a.SERIALID, a.QTY
      FROM SALESTRANS a
      UNION
      SELECT b.INVOICEDATE, b.SERIALID, b.QTY
      FROM SERVICETRANS b
     ) ab

I have two tables combined with UNION and my table looks:

I would like to make a query that returns SERIALID, QTY, but only for the last INVOICEDDATE.

The result I would like to get:

Thanks in advance!

Many databases support LEAST()/GREATEST(). Perhaps the most efficient method assuming there are no duplicates is:

WITH i AS (
      SELECT sat.INVOICEDATE, sat.SERIALID, sat.QTY
      FROM SALESTRANS sat
      UNION ALL
      SELECT set.INVOICEDATE, set.SERIALID, set.QTY
      FROM SERVICETRANS sat
     )
SELECT i.*
FROM (SELECT i.*,
             ROW_NUMBER() OVER (PARTITION BY SERIALID ORDER BY INVOICEDATE DESC) as seqnum
      FROM i
     ) i
WHERE seqnum = 1;

Note that if there are two invoices on the same date, this arbitrarily returns one of them. Use RANK() if you want both.

Note that this uses UNION ALL, not UNION. UNION incurs overhead for removing duplicates. That does not seem useful for a query like this.

Depending on your database (and data), there are possibly alternative ways of expressing this query, some might have better performance characteristics.