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

Select Max(DELIVERY_TIMESTAMP), Doc_Name From TableName Group By Doc_Name

That should do it, unless I missed something in the question.

Answer 2

SELECT
  DELIVERY_TIMESTAMP,
  OTHER_COLUMN
FROM
  DOCUMENTS
WHERE
  DELIVERY_TIMESTAMP = (SELECT MAX(DELIVERY_TIMESTAMP) FROM DOCUMENTS)

Answer 3

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

Answer 4

In MSSQL, the following works too:

SELECT TOP 1 * FROM DOCUMENTS ORDER BY DELIVERY_TIMESTAMP DESC

Answer 5

On some versions of SQL (i.e. MySQL) you can do this:

SELECT *
FROM DOCUMENTS
ORDER BY DELIVERY_TIMESTAMP DESC
LIMIT 1