segundo - numero de fila sql server

Consulta simple para obtener el valor máximo para cada ID

Question

OK tengo una mesa como esta:

ID Signal Station OwnerID
111 -120 Home 1
111 -130 Car 1
111 -135 Work 2
222 -98 Home 2
222 -95 Work 1
222 -103 Work 2

Esto es todo por el mismo día. Solo necesito la consulta para devolver la señal máxima para cada ID:

ID Signal Station OwnerID
111 -120 Home 1
222 -95 Work 1

Intenté usar MAX () y la agregación se confunde con que Station y OwnerID son diferentes para cada registro. ¿Necesito hacer una ÚNETE?

Answer 1

¿Algo como esto? Únase a su tabla consigo misma y excluya las filas para las que se encontró una señal más alta.

select cur.id, cur.signal, cur.station, cur.ownerid
from yourtable cur
where not exists (
select *
from yourtable high
where high.id = cur.id
and high.signal > cur.signal
)

Esto incluiría una fila para cada señal más alta, por lo que podría haber varias filas por id.

Answer 2

En el clásico SQL-92 (que no utiliza las operaciones OLAP utilizadas por Quassnoi), puede usar:

SELECT g.ID, g.MaxSignal, t.Station, t.OwnerID
FROM (SELECT id, MAX(Signal) AS MaxSignal
FROM t
GROUP BY id) AS g
JOIN t ON g.id = t.id AND g.MaxSignal = t.Signal;

(Sintaxis sin marcar; se supone que su tabla es ''t'').

La subconsulta en la cláusula FROM identifica el valor de señal máximo para cada id; la unión combina eso con la fila de datos correspondiente de la tabla principal.

NB: si hay varias entradas para una ID específica que tienen la misma potencia de señal y esa potencia es la MAX (), entonces obtendrá varias filas de salida para esa ID.

Probado contra IBM Informix Dynamic Server 11.50.FC3 ejecutándose en Solaris 10:

+ CREATE TEMP TABLE signal_info
(
id INTEGER NOT NULL,
signal INTEGER NOT NULL,
station CHAR(5) NOT NULL,
ownerid INTEGER NOT NULL
);
+ INSERT INTO signal_info VALUES(111, -120, ''Home'', 1);
+ INSERT INTO signal_info VALUES(111, -130, ''Car'' , 1);
+ INSERT INTO signal_info VALUES(111, -135, ''Work'', 2);
+ INSERT INTO signal_info VALUES(222, -98 , ''Home'', 2);
+ INSERT INTO signal_info VALUES(222, -95 , ''Work'', 1);
+ INSERT INTO signal_info VALUES(222, -103, ''Work'', 2);
+ SELECT g.ID, g.MaxSignal, t.Station, t.OwnerID
FROM (SELECT id, MAX(Signal) AS MaxSignal
FROM signal_info
GROUP BY id) AS g
JOIN signal_info AS t ON g.id = t.id AND g.MaxSignal = t.Signal;
111 -120 Home 1
222 -95 Work 1

Nombré la tabla Signal_Info para esta prueba, pero parece que produce la respuesta correcta. Esto solo muestra que hay al menos un DBMS que soporta la notación. Sin embargo, me sorprende un poco que MS SQL Server no lo haga. ¿Qué versión está usando?

Nunca deja de sorprenderme la frecuencia con la que se envían las preguntas de SQL sin nombres de tablas.

Answer 3

Podemos hacerlo usando auto-unirse

SELECT T1.ID,T1.Signal,T2.Station,T2.OwnerID
FROM (select ID,max(Signal) as Signal from mytable group by ID) T1
LEFT JOIN mytable T2
ON T1.ID=T2.ID and T1.Signal=T2.Signal;

O también puede utilizar la siguiente consulta

SELECT t0.ID,t0.Signal,t0.Station,t0.OwnerID
FROM mytable t0
LEFT JOIN mytable t1 ON t0.ID=t1.ID AND t1.Signal>t0.Signal
WHERE t1.ID IS NULL;

Answer 4

Usted está haciendo una operación de grupo máximo / mínimo. Esta es una trampa común: se siente como algo que debería ser fácil de hacer, pero en SQL no lo es.

Existen varios enfoques (tanto ANSI estándar como específicos del proveedor) para este problema, la mayoría de los cuales son subóptimos en muchas situaciones. Algunos le darán varias filas cuando más de una fila comparta el mismo valor máximo / mínimo; algunos no lo harán Algunos funcionan bien en mesas con un pequeño número de grupos; otros son más eficientes para un número mayor de grupos con filas más pequeñas por grupo.

Aquí hay una discusión de algunos de los más comunes (sesgados en MySQL pero generalmente aplicables). Personalmente, si sé que no hay máximos múltiples (o que no me importa conseguirlos) a menudo tiendo a adoptar el método de unión nula a la izquierda, que publicaré como nadie más lo ha hecho aún:

SELECT reading.ID, reading.Signal, reading.Station, reading.OwnerID
FROM readings AS reading
LEFT JOIN readings AS highersignal
ON highersignal.ID=reading.ID AND highersignal.Signal>reading.Signal
WHERE highersignal.ID IS NULL;

Answer 5

WITH q AS
(
SELECT c.*, ROW_NUMBER() OVER (PARTITION BY id ORDER BY signal DESC) rn
FROM mytable
)
SELECT *
FROM q
WHERE rn = 1

Esto devolverá una fila incluso si hay duplicados de MAX(signal) para una ID determinada.

Tener un índice en (id, signal) mejorará enormemente esta consulta.

Answer 6

select a.id, b.signal, a.station, a.owner from
mytable a
join
(SELECT ID, MAX(Signal) as Signal FROM mytable GROUP BY ID) b
on a.id = b.id AND a.Signal = b.Signal 

Answer 7

with tab(id, sig, sta, oid) as
(
select 111 as id, -120 as signal, ''Home'' as station, 1 as ownerId union all
select 111, -130, ''Car'', 1 union all
select 111, -135, ''Work'', 2 union all
select 222, -98, ''Home'', 2 union all
select 222, -95, ''Work'', 1 union all
select 222, -103, ''Work'', 2
) ,
tabG(id, maxS) as
(
select id, max(sig) as sig from tab group by id
)
select g.*, p.* from tabG g
cross apply ( select top(1) * from tab t where t.id=g.id order by t.sig desc ) p

Answer 8

SELECT * FROM StatusTable WHERE Signal IN ( SELECT A.maxSignal FROM ( SELECT ID, MAX(Signal) AS maxSignal FROM StatusTable GROUP BY ID ) AS A );