Consulta simples para obter o valor máximo para cada ID

OK, eu tenho uma 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 

Isso tudo é para o mesmo dia. Eu só preciso da consulta para retornar o sinal máximo para cada ID:

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

Eu tentei usar MAX () ea agregação bagunça com o Station e OwnerID sendo diferente para cada registro. Preciso fazer um JOIN?

Algo assim? Junte-se à sua tabela e exclua as linhas para as quais um sinal mais alto foi encontrado.

 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 ) 

Isso listaria uma linha para cada sinal mais alto, portanto, pode haver várias linhas por id.

Você está fazendo uma operação máxima / mínima de grupo. Esta é uma armadilha comum: parece algo que deve ser fácil de fazer, mas no SQL isso não é agravante.

Há várias abordagens (tanto padrão ANSI quanto específicas do fornecedor) para esse problema, a maioria das quais é sub-ótima em muitas situações. Alguns lhe fornecerão várias linhas quando mais de uma linha compartilhar o mesmo valor máximo / mínimo; alguns não. Alguns funcionam bem em tabelas com um pequeno número de grupos; outras são mais eficientes para um número maior de grupos com linhas menores por grupo.

Aqui está uma discussão de alguns dos mais comuns (MySQL-tendenciosa, mas geralmente aplicável). Pessoalmente, se eu sei que não há vários máximos (ou não me importo em obtê-los), geralmente tento o método auto-join null-left-self, que postarei como se ninguém mais tivesse:

 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; 

No clássico SQL-92 (não usando as operações OLAP usadas pelo Quassnoi), você pode 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; 

(Sintaxe desmarcada; assume que sua tabela é ‘t’.)

A subconsulta na cláusula FROM identifica o valor máximo do sinal para cada id; a junit combina isso com a linha de dados correspondente da tabela principal.

NB: se houver várias inputs para um ID específico que tenham a mesma intensidade de sinal e que a força seja o MAX (), você obterá várias linhas de saída para esse ID.


Testado em relação ao IBM Informix Dynamic Server 11.50.FC3 em execução no 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 

Eu nomeei a tabela Signal_Info para este teste – mas parece produzir a resposta certa. Isso mostra apenas que há pelo menos um DBMS que suporta a notação. No entanto, estou um pouco surpreso que o MS SQL Server não – qual versão você está usando?


Nunca deixa de me surpreender com que frequência as questões SQL são submetidas sem nomes de tabelas.

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
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 
 WITH q AS ( SELECT c.*, ROW_NUMBER() OVER (PARTITION BY id ORDER BY signal DESC) rn FROM mytable ) SELECT * FROM q WHERE rn = 1 

Isso retornará uma linha mesmo se houver duplicatas de MAX(signal) para um determinado ID .

Ter um índice em (id, signal) irá melhorar muito esta consulta.

 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 

Nós podemos fazer usando auto join

 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; 

Ou você também pode usar a seguinte 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; 
 SELECT * FROM StatusTable
 ONDE Sinal IN (
     SELECIONE A.maxSignal FROM
     (
         SELECT ID, MAX (sinal) AS maxSignal
         FROM StatusTable
         GROUP BY ID
     ) COMO UM
 );