SQL Searching by MAX()

Question

select max(DELIVERY_TIMESTAMP) from DOCUMENTS; will return the time that the latest document was delivered. How do I return the other columns for the latest document? For example I want DOC_NAME for the document that was most recently delivered?

I'm not sure how to form the WHERE clause.

Answer 1

You have a few options

SELECT DOC_NAME
FROM DOCUMENTS
WHERE DELIVERY_TIMESTAMP IN (
    SELECT MAX(DELIVERY_TIMESTAMP)
    FROM DOCUMENTS
)

Or with joins

SELECT DOC_NAME
FROM DOCUMENTS
INNER JOIN (
    SELECT MAX(DELIVERY_TIMESTAMP) AS MAX_DELIVERY_TIMESTAMP
    FROM DOCUMENTS
) AS M
    ON M.MAX_DELIVERY_TIMESTAMP = DOCUMENTS.DELIVERY_TIMESTAMP

It gets more complicated if there are duplicates in a timestamp or you need multiple columns in your "max" criteria (because MAX() is only over the one column for all rows)

This is where the JOIN option is the only option available, because a construction like this is not available (say multiple orders with identical timestamp):

SELECT DOC_NAME
FROM DOCUMENTS
WHERE (DELIVERY_TIMESTAMP, ORDERID) IN (
    SELECT TOP 1 DELIVERY_TIMESTAMP, ORDERID
    FROM DOCUMENTS
    ORDER BY DELIVERY_TIMESTAMP DESC, ORDERID DESC
)

Where you in fact, would need to do:

SELECT DOC_NAME
FROM DOCUMENTS
INNER JOIN (
    SELECT TOP 1 DELIVERY_TIMESTAMP, ORDERID
    FROM DOCUMENTS
    ORDER BY DELIVERY_TIMESTAMP DESC, ORDERID DESC
) AS M
    ON M.DELIVERY_TIMESTAMP = DOCUMENTS.DELIVERY_TIMESTAMP
        AND M.ORDERID = DOCUMENTS.ORDERID