SELECT <all the fields you need> ; FROM MyTable ; JOIN (SELECT MAX(ORDER_NO) AS MaxOrd FROM ; MyTable MTB; JOIN (SELECT MAX(DATE_FLD) As MaxDate FROM MyTable MTC) csrMaxDate ; ON MTB.DateFld = csrMaxDate.MaxDate) csrMaxOrdNo ; ON MyTable.ORDER_NO = csrMaxOrdNo.MaxOrdThis has nested queries. You have to work from the inside out. The innermost query is:
SELECT MAX(DATE_FLD) As MaxDate FROM MyTable MTCNo join clause at all, since it's just one table. When you look outside the parentheses that surround this one, you see that it's creating a (temporary) cursor named csrMaxDate. The next query out joins csrMaxDate to the original table:
SELECT MAX(ORDER_NO) AS MaxOrd FROM ; MyTable MTB; JOIN (SELECT MAX(DATE_FLD) As MaxDate FROM MyTable MTC) csrMaxDate ; ON MTB.DateFld = csrMaxDate.MaxDateJust one join in this query, joining the MyTable using alias MTB with csrMaxDate. This query creates a (temporary) cursor named csrMaxOrdNo. Finally, the main query joins MyTable to csrMaxOrdNo:
FROM MyTable ; JOIN (SELECT MAX(ORDER_NO) AS MaxOrd FROM ; MyTable MTB; JOIN (SELECT MAX(DATE_FLD) As MaxDate FROM MyTable MTC) csrMaxDate ; ON MTB.DateFld = csrMaxDate.MaxDate) csrMaxOrdNo ; ON MyTable.ORDER_NO = csrMaxOrdNo.MaxOrdAs I write this, I realize that since it's SQL Server, we could have used CTEs to make the whole thing more readable like this:
WITH csrMaxDate (MaxDate) AS (SELECT MAX(DATE_FLD) As MaxDate FROM MyTable), csrMaxOrdNo (MaxOrd) AS (SELECT MAX(ORDER_NO) AS MaxOrd FROM MyTable JOIN csrMaxDate ON MyTable.DateFld = csrMaxDate.MaxDate) SELECT <all the fields you need> ; FROM MyTable JOIN csrMaxOrdNo ON MyTable.ORDER_NO = csrMaxOrdNo.MaxOrdI think that's a lot more readable and easier to understand.