SOQL - how to get the first occurrence of a record, grouped by accounts

Question

I have a table of assets

I need to get only 1 asset from each account. Doesn't matter which asset.

The end result should be: (In this scenario)

What I tried:

• SELECT Id, accountid, MAX(CreatedDate) FROM Asset WHERE Status != 'Cancelled' group by id, accountid

Other formats gave same solution.

So, what is the correct way to get what I want?

Answer 1

Try this one.

select id, (select id from Assets where status != 'Cancelled' order by Name limit 1) from account

Here you have option to sort the assets to get the right one.

Or if the order does not matter you can modify the query you provided but there is limit for 2000 lines returned.

SELECT max(Id), accountid FROM Asset WHERE Status != 'Cancelled' group by accountid