SQL Search by MAX ()

Question

select max(DELIVERY_TIMESTAMP) from DOCUMENTS; will return the delivery time of the last document. How to return other columns for the last document? For example, I want DOC_NAME for a document that was recently delivered?

I am not sure how to create a WHERE .

Answer 1

You have several options.

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

Or with associations

 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 

This becomes more complicated if there are duplicates in the timestamp or you need several columns in your "max" criteria (because MAX() is only above one column for all rows)

Here the JOIN option is the only option available, because such a design is not available (for example, several orders with the same time stamp):

 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 ) 

If you really need to:

 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 2

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

Answer 3

The following also works in MSSQL:

 SELECT TOP 1 * FROM DOCUMENTS ORDER BY DELIVERY_TIMESTAMP DESC 

Answer 4

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

 SELECT * FROM DOCUMENTS ORDER BY DELIVERY_TIMESTAMP DESC LIMIT 1 

Answer 5

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

This should do it if I have not missed something in this matter.