Ordering by top result of a nested order by

Question

First off, I'm working with an EAV database so when I say entity, it's the same as a table.

I have an "asset" entity and each asset has many "offers". Each offer has a price attribute. I want to return the offer with the top price for each asset and this must be ordered also by top price. So for example:

Asset 1 has 3 offers (a, b and c) with price $4 (a), $7.25 (b) and $5 (c)

and

Asset 2 has 2 offers (d and e) with price $9 (d) and $3 (e)

So the result set should look like:

Offer       Price       Asset  
d           $9           2  
b           $7.25        1

The reason I have not provided any SQL is because I am working on a non-relational database where everything is held in 4 tables - entity table, subtype table, metastring and metadata. But I just want the conceptual answer as if this was a relational database and I can translate this to EAV SQL.

Answer 1

Something like this?

SELECT    data.Asset, data.Price, o.offerID
FROM      (
          SELECT o.assetID AS Asset, MAX(o.offer) AS Price
          FROM   assetTable AS a
                 INNER JOIN offerTable AS o
                     ON a.assetID = o.assetID    
          GROUP BY o.assetID
          ) data
          INNER JOIN offerTable AS o
              ON data.Asset = o.assetID
              AND data.Price = o.offer
ORDER BY  data.Price DESC, data.assetID