SQL - Query max value of a group by three columns

You can simply use ROW_NUMBER :

WITH CTE AS
(
    SELECT  *,
            ROW_NUMBER() OVER(PARTITION BY secuencialGarantia
                              ORDER BY deudaInicial DESC) RN
    FROM #temp_Garantias
)
SELECT *
FROM CTE
WHERE RN = 1;

You do not have to group. To obtain the value of the field numeroSolicitud that corresponds to the highest deudaInicial , what you can do is to imitate the results to 1 row with the clause top 1 and sort by deudaInicial downwards.

The query would be like the following:

select top 1 numeroSolicitud, secuencialGarantia, deudaInicial
  from #temp_Garantias 
 where secuencialGarantia = 42
 order by deudaInicial desc;

I've written this one to show the result:

with
Garantias as (
select 925866 numeroSolicitud, 5 secuencialGarantia, 19000.0 deudaInicial
union all select 929472, 42, 3000
union all select 924842, 42, 7000
union all select 924988, 42, 29000
union all select 926715, 42, 8000
union all select 923169, 45, 23100
union all select 927939, 47, 50000
)
select top 1 numeroSolicitud, secuencialGarantia, deudaInicial
  from Garantias
 where secuencialGarantia = 42
 order by deudaInicial desc;

that returns:

numeroSolicitud secuencialGarantia deudaInicial
--------------- ------------------ --------------
924988          42                 29000.0

(1 row affected)

which is the data you are looking for.

What indicates the error is that the column numeroSolicitud is not inside the clause GROUP BY or at least contained within an aggregate function (for example the function MAX that you use is an aggregate function).

What you should do is add the name of the column GROUP BY to the clause numeroSolicitud .

It would stay like this:

SELECT numeroSolicitud, secuencialGarantia, max(deudaInicial) FROM #temp_Garantias 
GROUP BY numeroSolicitud, secuencialGarantia

Updating :

For what you put in your edition, you need the maximum debt with the minimum request number, if so, try this query.

SELECT MIN(numeroSolicitud), secuencialGarantia, max(deudaInicial) FROM #temp_Garantias 
GROUP BY secuencialGarantia

I leave you some references:

• Group By
• Aggregate functions

If you have SQL Server 2008 or higher, you could generate a row numerator by ROW_NUMBER() , partitioning it by secuencialGarantia and sorting by deudaInicial descending, which should start this number in 1 just in the cases where the Debt is higher.

SELECT  *
    FROM (SELECT    numeroSolicitud, 
            secuencialGarantia, 
            deudaInicial,
            ROW_NUMBER() OVER (PARTITION BY secuencialGarantia, ORDER BY deudaInicial DESC) AS RN
            FROM #temp_Garantias
    ) T
    WHERE RN = 1

See documentation for SELECT - OVER Clause (Transact-SQL)