Is there any way to get the max based on two criteria or one criteria and a group by? so for example I have a table with 3 fields, [GroupByVariable], [Conc], and [D_Conc];
GroupByVariable Conc D_Conc Sediment 25 1 Sediment 30 0 Sediment 20 1 Soil 17 1 Soil 8 0
I want a query to get the max of field [Conc] based on [D_Conc] = 1 and [GroupByVariable]. It would look something like this...
GroupByVariable MaxConcBasedOnD_Conc1 Sediment 25 Soil 17
I'd like to do this in one query and also not by crosstab because I want to keep building...like get min/max/avg/count/ for each [D_Conc] 0 and 1 and then a total count for all 0 and 1.