SQL: SELECT rows with max date from created table

Question

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:

Table AB

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

The result I would like to get:

Result

Thanks in advance!

You have first to GROUP BY ab.SERIALID and after add WHERE ab.INVOICEDATE is equal to the MAX(ab.INVOICEDATE) — luna80, Feb 16 '20 at 06:59

PSK · Answer 1 · 2020-02-16T07:18:12.577

If your RDBMS support ROW_NUMBER(), you can achieve this using ROW_NUMBER() PARTITION BY like following.

SELECT *
FROM (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY SERIALID ORDER BY INVOICEDATE DESC
            ) RN
    FROM SERVICETRANS
    ) T
WHERE RN = 1

Above query will return you 1 record for each SERIALID having max INVOICEDATE

Same can be done without ROW_NUMBER() like following.

SELECT *
FROM SERVICETRANS ST
WHERE INVOICEDATE = (
        SELECT MAX(INVOICEDATE)
        FROM SERVICETRANS STI
        WHERE STI.SERIALID = ST.SERIALID
        )

Note: Above query may return multiple rows for one SERIALID if the MAX(INVOICEDATE) is same for multiple records of same SERIALID.

Thanks but, unfortunately, I can´t use this solution because I need to get unique SERIALID column. — Vladas Zigelis, Feb 24 '20 at 05:48

score 0 · Accepted Answer · answered Feb 16 '20 at 13:11

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.

SQL: SELECT rows with max date from created table

2 Answers2