I have a table of assets enter image description here

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

The end result should be: (In this scenario) enter image description here

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?

1 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .