The inner and outer selects should not be on the same object type for Quote object

Question

Possible duplicate of This or This but didn't get any solution there. I want to select records from Quote with max LastModifiedDate as below,

SELECT Id,AccountId,BillingAddress FROM Quote where LastModifiedDate in (SELECT MAX(LastModifiedDate) FROM Quote)

But its giving

The inner and outer selects should not be on the same object type

Highly appreciate any help.

Answer 1

The error itself is pretty descriptive, but even if you could use the same table, these joins only work on Id values, not any other field type. You need two separate queries. For instance, you can do the following in Apex:

Datetime max = [
    SELECT LastModifiedDate FROM Quote
    ORDER BY LastModifiedDate DESC LIMIT 1
];
List<Quote> records = [
    SELECT AccountId, BillingAddress FROM Quote
    WHERE LastModifiedDate = :max
];

Note that since it is a Datetime field, collisions are extremely unlikely unless you mostly perform bulk operations. If you aren't expecting multiple records and actually just want the most recent, then the first query will suffice.

Answer 2

You can find the most recently modified Quote without a sub-select using an ORDER BY and LIMIT clause:

SELECT Id, AccountId, BillingAddress FROM Quote ORDER BY LastModifiedDate DESC LIMIT 1

If your objective is to find all Quotes sharing that date, you'll need ot use two queries as Adrian's answer shows.