PostgreSQL：仅显示最大值 [英] Postgresql: Show only the max value

问题描述

我有以下查询，该查询向我返回容器的移动，名称和容器的提示。容器上的冰柱最大值表示相同的冰柱。

i have the following query that returns me the move, name of a container and the cicle of the container. The max value of the cicle on the container means that is the actual cicle of the same.

select     des.movimiento, 
           des.equipo_identi, 
           max(des.ciclo) as ciclo
from       publico.descarga des
inner join publico.prioridad_movimiento primo 
on         des.movimiento = primo.movimiento
group by   des.movimiento, des.equipo_identi, primo.prioridad
order by   primo.prioridad

它返回给我

 movimiento    | equipo_identi | ciclo
--------------- --------------- --------
Descarga       | CBHU4329906   | 1 
Descarga       | CSLU1387094   | 2 
Descarga       | CSLU1407729   | 2 
GateOut Puerto | CBHU4329906   | 1 
GateOut Puerto | CSLU1387094   | 2 
GateOut Puerto | CSLU1407729   | 2 
GateIn Patio   | CBHU4329906   | 1 
GateIn Patio   | CSLU1387094   | 2 
GateIn Patio   | CSLU1407729   | 2 

---

我要显示的是最后一个最后一次移动（ movimiento ）上的cicle（ ciclo ）。这样，显示这3个不同的容器（表标题：equipo_identi ），最后一个动作是 GateIn Patio：

---

What i want to is to show the last cicle(ciclo) on the last movement(movimiento) it has. Like this, shows these 3 different containers(table title: equipo_identi) that the last movement was "GateIn Patio":

  movimiento | equipo_identi | ciclo
------------- --------------- --------
GateIn Patio | CBHU4329906   | 1
GateIn Patio | CSLU1387094   | 2 
GateIn Patio | CSLU1407729   | 2 

---

为此，我有下表设置我的运动优先级。

---

For this, i have the following table that set my movement priority.

prioridad | movimiento
---------- ----------------
1         | Descarga
2         | GateOut Puerto
3         | GateIn Patio
4         | GateOut Patio
5         | GateIn Puerto
6         | Export

最大优先级为6，最小优先级为1。

The max priority is 6 and the min is 1.

推荐答案

如果我理解正确，您只想在汇总后按优先级对结果进行排名：

If I understand correctly, you just want to rank the results by priority after the aggregation:

select x.*
from (select des.movimiento, des.equipo_identi, max(des.ciclo) as ciclo,
             rank() over (order by primo.prioridad desc) as rnk
      from publico.descarga des inner join
           publico.prioridad_movimiento primo 
           on des.movimiento = primo.movimiento
      group by des.movimiento, des.equipo_identi, primo.prioridad
     ) x
where rnk = 1;

这篇关于PostgreSQL：仅显示最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！