I have a fact table with a number of foreign keys to dimension data and some measure columns. Let's assume I've got dimension columns a,b,c,d,e, and f and measure columns x, and y.
I need to be able to find the value of f, x, and y for the row with min(x/y) when e in (1,2) and max(x/y) when e not in (1,2) with grouping by a,b,c, and d.
A query which would identify the row in question would look like this:
select min(x/y), a, b, c, d, e from my_table group by a,b,c,d,e order by a,b,c,d
But I don't know how to go about also extracting the value of x, y, and f for the row which is matched - I suppose I could join to a subquery, but that strikes me as probably not being the most efficient way to accomplish it.
Actually, it occurs to me that I've potentially got more than one row for any given a,b,c,d,e,f tuple, but I want a min for all rows aggregated by f
Basically, for each (a,b,c,d,e) I want to return sum(x), sum(y), and f (as well as a,b,c,d,e) for the f with min(sum(x)/sum(y))