hi everyone. hope you all had a great christmas! i'm having trouble converting an example mysql script into a solr query. here's my preliminary query:
select vendorItem, min(unitPrice), max(unitPrice), -(min(unitPrice) - > max(unitPrice)) as `diff` > from transactions > where orgId IN (x,y,z) > and vendor LIKE "%FooBar%" > group by vendorItem, uom > having count(distinct(facilityId)) > 1 > order by (min(unitPrice) - max(unitPrice)); here's the use case/business need: some purchasing entities exist within a group. the group organization wants to see where they are getting hosed on pricing between their child entities. for instance, entity A pays $100 for a product while entity B pays $150 for the same product from the same vendor. i'd conceived of using stats' min and max, faceting, and/or a function query to determine the difference between min and max from a given result set and order by it, but have yet to come up w something that seems to work. any suggestions? thanks!
