SQL question (get rows by aggregate result?)

Delzhand

I have a 50 line sql query that returns a dataset with the following fields:

membership_name | membership_id | membership_min_fee | total_amount | member_id

Basically, all the memberships (and their ids and minimum fees) where the minimum fee is <= total amount, for any given member_id.

Every member_id and total_amount are always matched, as are membership_name/id/min_fee.

Looks like this:

Basic   1   25.00  100.00 mem001
Silver   2   50.00  100.00 mem001
Gold   3  100.00  100.00 mem001
Basic   1   25.00  50.00 mem002
Silver   2   50.00  50.00 mem002
etc

I want to get the only those rows where the minimum fee is the max for the given member_id. I can usually achieve this by using MAX(membership_min_fee)/GROUP BY member_id, but that only gives me the max(min_fee) and member_id. I also want the matching membership_id, which I can't group by, because then I get the highest min_fee for each member_id/membership_id combo, i.e., what I already have.

Edit: I'd prefer, if possible not to query the dataset against itself, because it's already such a huge query.

Edit 2: My backup plan is to just query the membership types table against the min_fee, but if I can do this with one less subquery, even better.

Feedus

Use the having clause http://techonthenet.com/sql/having.php maybe ? Hard to tell exactly what is going on without the query.

Deebaser

If you don't want to use subqueries, can't you just create a temp table with the max function then join that on the dataset?

Deebaser

Yeah, the column names/ data are making your objective really hard to parse out.

Delzhand

Well, I've got it to do what I want, but once I finish up this stuff this will become a "help me improve this query" thread.

exmello

just inner join the group by result back onto itself.

select * from
(<original query>
group by blah
) as groupquery
inner join othertable
on groupquery.member_id = othertable.member_id

If you're having performance problems or it looks too messy, break it down into steps using a table variable.

Chickeen

If you're using SQL 2008, this query sounds like a good candidate for the RANK function.

vonPoonBurGer

Oracle and PostgreSQL also have implementations of RANK, and I'll second Chickeen's advice, it really sounds like RANK could make this query a lot simpler.