SQL Bring top 1 or max

Question

SQL Bring top 1 or max

Navigation

#1 by (1 votes)

1

I have the following query:

select  top 1 fact.vend, fact.cliente, sum((detalle.precio-art.costo)*cant) as total
from afacturas as fact
inner join afacart as detalle on fact.comprob = detalle.comprob
inner join aarticulos as art on detalle.cordart = art.codigo
group by fact.cliente,fact.vend  
order by vend

The one that returns the accumulated total for each client that each vendor has:

1   1   1800
1   12  4800
2   5   2300
2   11  42500
3   2   4600
3   4   1700000
4   16  160000

What I want to do is to return only the top 1 of each customer that has the highest amount of money per seller.

For example:

1   12  4800
2   11  42500
3   4   1700000
4   16  160000

sql sql-server query

asked by Oren Diaz 26.11.2017 в 16:44

source

1 answer

Get res.locals values in another midleware How to use a sharePreferences with a tableLayout

score 1 · Accepted Answer

First, it is not possible that your first query with the TOP 1 returns the results you say. You must be referring to the same query, but without the TOP 1 clause.

As for the solution to your problem, you can add to your SELECT a call to the window function ROW_NUMBER to assign a rank to the records per seller ( partition by fact.vend ) according to the highest amount of silver ( order by sum((detalle.precio-art.costo)*cant) desc ). Then, it's a matter of just returning the records that have a range of 1 ( where rnk = 1 ):

;with cte as (
  select fact.vend,
         fact.cliente,
         sum((detalle.precio-art.costo)*cant) as total,
         row_number() over (
           partition by fact.vend
               order by sum((detalle.precio-art.costo)*cant) desc) as rnk
    from afacturas as fact
   inner join afacart as detalle on fact.comprob = detalle.comprob
   inner join aarticulos as art on detalle.cordart = art.codigo
   group by fact.cliente,fact.vend
)
select vend, cliente, total
  from cte
 where rnk = 1
 order by vend