Sql-server – Group by multiple columns, agregate others and select all in SQL Server

aggregategroup bypartitioningsql server

I am using SQL server and I can't seem to construct the query I want. I have a table with several columns, among them are

PARAMETER_NAME, GW_LOCATION_ID, Report_Result, DETECT_FLAG

I want a query to return

a row for each unique Parameter-Location combination
the maximum value of the Report_Result column for that unique Parameter-Location combination and
the DETECT_FLAG value associated with the maximum value.

There are other columns in addition to DETECT_FLAG that I want to return but I think that if I can get this part worked out, I should be able to return the other columns similarly.

The query works if I group by PARAMETER_NAME and GW_LOCATION_ID and aggregate the Report_Result column (see below). However, when I add the DETECT_FLAG column, I get the error, "Column 'SLVs_Flagged.DETECT_FLAG' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." What I want is the value of DETECT_FLAG for the row returned by MAX(Report_Result).

SELECT 
PARAMETER_NAME, GW_LOCATION_ID, MAX(Report_Result)
FROM SLVs_Flagged
GROUP BY PARAMETER_NAME, GW_LOCATION_ID
ORDER BY PARAMETER_NAME, GW_LOCATION_ID;

I have tried to do a subselect to return the DETECT_FLAG value that corresponds to MAX(Report_Result) but I try to use a WHERE condition and I get another error. Please advise how I can execute this query.

Here is a subset of the data for testing.

PARAMETER_NAME  GW_LOCATION_ID  Report_Result   DETECT_FLAG
Perchlorate CDBO-6  2.38    N
Perchlorate CDBO-6  1.45    N
Perchlorate CDV-16-02655    4   N
Perchlorate CDV-16-02655    0.537   Y
Perchlorate CDV-16-02655    4   N
Perchlorate CDV-16-02656    100 N
Perchlorate CDV-16-02656    0.394   Y
Perchlorate CDV-16-02656    4   N
Perchlorate CDV-16-02656    4   N
Perchlorate CDV-16-02657    4   N
Perchlorate CDV-16-02657    4   N
Perchlorate CDV-16-02657    4   N
Perchlorate CDV-16-02657    0.174   Y
Perchlorate CDV-16-02658    4   N
Perchlorate CDV-16-02658    4   Y
Perchlorate CDV-16-02658    0.126   Y
Perchlorate CDV-16-02658    0.0561  Y
Perchlorate CDV-16-02658    20  N
Perchlorate CDV-16-02658    4   N
Perchlorate CDV-16-02659    4   N
Nitrate as Nitrogen R-16 S4 0.003   N
Nitrate as Nitrogen R-20 S1 0.003   N
Nitrate as Nitrogen R-20 S1 0.003   N
Nitrate as Nitrogen R-20 S1 0.003   N
Nitrate as Nitrogen R-20 S2 0.003   N
Nitrate as Nitrogen R-20 S2 0.003   N
Nitrate as Nitrogen R-20 S3 0.003   N
Nitrate as Nitrogen R-20 S3 0.003   N
Nitrate as Nitrogen R-20 S3 0.003   N
Nitrate as Nitrogen R-27    0.003   N
Nitrate as Nitrogen R-31 S2 0.003   N
Nitrate as Nitrogen R-32 S3 0.003   N
Nitrate as Nitrogen R-32 S3 0.003   N
Nitrate as Nitrogen Test Well 1A    0.01    N
Nitrate as Nitrogen Test Well 1A    -0.01   N
Nitrate as Nitrogen Test Well 2 0.04    N
Nitrate as Nitrogen Test Well 2 0.01    N
Nitrate as Nitrogen Test Well 2A    0   N
Nitrate as Nitrogen Test Well 3 0.04    N
Nitrate as Nitrogen Test Well 3 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 8 0.04    N
Nitrate as Nitrogen Test Well 8 0.04    N

Best Answer

This is a greatest-n-per-group problem and there are many ways to solve it (CROSS APPLY, window functions, subquery with GROUP BY, etc). Here's a method using window functions and a CTE:

WITH ct AS
  ( SELECT *,
           rn = RANK() OVER (PARTITION BY PARAMETER_NAME, GW_LOCATION_ID
                             ORDER BY Report_Result DESC)
    FROM SLVs_Flagged
  )
SELECT PARAMETER_NAME, GW_LOCATION_ID, 
       Max_Report_Result = Report_Result,
       DETECT_FLAG
       -- more columns
FROM ct
WHERE rn = 1
ORDER BY PARAMETER_NAME, GW_LOCATION_ID ;

The query will return all tied results (if there are ties). If you want a single result for every (PARAMETER_NAME, GW_LOCATION_ID) combination, you can resolve ties by using ROW_NUMBER() instead of RANK() and modifying the ORDER BY inside the OVER (..) clause. Eg. (prefer DETECT_FLAG with N over Y):

           rn = ROW_NUMBER() OVER (PARTITION BY PARAMETER_NAME, GW_LOCATION_ID
                                   ORDER BY Report_Result DESC, DETECT_FLAG)

Related Solutions

Postgresql – Error “column does not exist” in a SELECT with JOIN and GROUP BY query

You probably want something like this:

SELECT h_v_charges.*, 
       last_v.last_version
FROM hist_versions_charges h_v_charges 
  JOIN (select proj_charge_id, 
               max(version) as last_version
        from hist_versions_charges 
        where proj_sous_projet_id = 2  
        group by proj_charge_id
  ) last_v  
  ON h_v_charges.version = last_v.last_version
 AND h_v_charges.proj_charge_id = last_v.proj_charge_id 
ORDER BY h_v_charges.proj_charge_id ASC;

A possibly (because no join is required) faster solution would be:

select *
from (
   select hvc.*, 
          row_number() over (partition by proj_charge_id order by version desc) as rn
   from hist_versions_charges as hvc
   where proj_sous_projet_id = 2  
) as hv
where rn = 1
order by hv.proj_charge_id ASC;

As Colin has pointed out, this can also be written as:

with hv as (
  select hvc.*, 
         row_number() over (partition by proj_charge_id order by version desc) as rn
  from hist_versions_charges as hvc
  where proj_sous_projet_id = 2  
) 
select *
from hv
where rn = 1
order by hv.proj_charge_id ASC;

Mysql – Is it possible to reduce a group to a row based on some criteria

The traditional solution, the one you may find in books, is to do a self join: first find that "max date per group" of yours, then join to self table on rows with said max date.

However, some hacks allow you to avoid that. Consider the following query:

SELECT
  MAX(date_added) AS date_added,
  SUBSTRING_INDEX(GROUP_CONCAT(some_column ORDER BY date_added DESC), ',', 1) AS some_column,
  SUBSTRING_INDEX(GROUP_CONCAT(another_column ORDER BY date_added DESC), ',', 1) AS another_column
FROM t
GROUP BY whatever

GROUP_CONCAT is an aggregation function which implodes values onto one string. It allows for ORDER BY, which we utilize via ORDER BY date_added DESC so as to implode our desired value first. We then slice up the first token in the string via SUBSTRING_INDEX.

The downside here (apart from making the query quite the frightening appearance) is that your numerical values are transformed into texts. Typically no big deal with SQL, but please be aware.

There's another option where you do a semi-self-join, a much lighter one; you will have to give up usage of index. It's quite long to describe; it still uses GROUP_CONCAT and SUBSTRING_INDEX, but only for the purpose of creating a derived table with only relevant keys. This derived table is then joined with original table. See an example in SQL: selecting top N records per group, another solution.

Best Answer

Related Solutions

Postgresql – Error “column does not exist” in a SELECT with JOIN and GROUP BY query

Mysql – Is it possible to reduce a group to a row based on some criteria

Related Question