Aggregate Query and display highest value of non-aggregated columns-CodePudding

how you doing? I've this question: Is it possible to aggregate a query with GroupBy on #col1 and then display the value of #col2 with the highest date?

It's a problem that I've come accross multiple times and I've always got by using some logic in my code, but if it is possible it's nice to know.

Let me give you a practical example: This query is pretty straightforward: It gets all the salary elements of a given person

SELECT dipElem.CodDip,
   dipElem.CodElemRetributivo as salElem,
   FORMAT(dipElem.DataInizioValidita, 'yyyy-MM-dd') AS datainizio,
   CONVERT(DECIMAL(10, 2), dipElem.ElemRetributivo) AS importo 
FROM JBADipendElemRetrib dipElem
     INNER JOIN JBAElemRetr elemRetr ON elemRetr.CodElemRetributivo = 
dipElem.CodElemRetributivo
WHERE dipElem.CodDitta = :CodDitta
    AND elemRetr.CodContratto = :CodContratto
    AND dipElem.CodDipendente = :CodDipendente
    AND dipElem.DBGruppo = 'GG'
ORDER BY dipElem.CodDipendente,
    dipElem.CodElemRetributivo ASC,
    dipElem.DataInizioValidita ASC;

And returns this:

CodDip	salElem	datainizio	importo
9	1	2019-11-04	989.18
9	1	2020-01-01	989.18
9	1	2020-11-01	1201.14
9	1	2021-11-01	1314.19
9	2	2019-11-04	0.00
9	5	2019-11-04	0.00
9	6	2019-11-04	0.00

But, I want to return something like that: Grouped by salElem, with the importo of the highest datainizio possible for that salElem.

CodDip	salElem	datainizio	importo
9	1	2021-11-01	1314.19
9	2	2019-11-04	0.00
9	5	2019-11-04	0.00
9	6	2019-11-04	0.00

CodePudding user response：

This is a generic greatest-n-per-group query. In MySQL 8 or later you can use row_number window function:

with cte as (
    select t.*, row_number() over (partition by salElem order by datainizio desc) as rn
    from t
)
select *
from t
where rn = 1

CodePudding user response：

To wrap up the question and follow Salman A's answer here is the code (based on my question) that resolves the problem:

WITH tmpTable AS (
    SELECT dipElem.CodElemRetributivo,
    FORMAT(dipElem.DataInizioValidita, 'yyyy-MM-dd') AS datainizio,
    CONVERT(DECIMAL(10, 2), dipElem.ElemRetributivo) AS importo,
    ROW_NUMBER() OVER (PARTITION BY dipElem.CodElemRetributivo
    ORDER BY dipElem.DataInizioValidita DESC)    AS my_rank
    FROM JBADipendElemRetrib dipElem
         INNER JOIN JBAElemRetr elemRetr ON elemRetr.CodElemRetributivo = dipElem.CodElemRetributivo
    WHERE dipElem.CodDitta = :CodDitta
        AND elemRetr.CodContratto = :CodContratto
        AND dipElem.CodDipendente = :CodDipendente
        AND dipElem.DBGruppo = 'GG'
)
SELECT *
FROM tmpTable
WHERE my_rank = 1;