Requête simple pour saisir la valeur maximale pour chaque ID

OK j'ai un tableau comme ceci:

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

C'est tout pour le même jour. J'ai juste besoin de la Requête pour renvoyer le signal max pour chaque ID:

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

J'ai essayé d'utiliser le MAX() et l'agrégation des mess avec la Station et de OwnerID étant différentes pour chaque enregistrement. Ai-je besoin de faire une JOINTURE?

source d'informationauteur Nick Sinas

15

Quelque chose comme cela? Joindre à votre table avec elle-même, et d'exclure les lignes pour lesquelles un signal plus élevé a été trouvé.
```
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
)
```
Cette liste une ligne pour chaque signal la plus haute, alors il pourrait y avoir plusieurs lignes par id.
14

Vous faites un groupe de sages-maximum/minimum de fonctionnement. C'est un piège commun: il se sent comme quelque chose qui devrait être facile à faire, mais en SQL il aggravatingly ne l'est pas.

Il y a un certain nombre d'approches (standard ANSI et les fournisseurs spécifiques) à ce problème, dont la plupart sont sous-optimales dans de nombreuses situations. Certains vous donneront de multiples lignes pendant plus d'une ligne de partage le même maximum/minimum de la valeur; d'autres ne le seront pas. Certains fonctionnent très bien sur les tables avec un petit nombre de groupes; d'autres sont plus efficaces pour un grand nombre de groupes avec un plus petit nombre de lignes par groupe.

Voici une discussion de certains des plus communs (MySQL-biaisé, mais généralement le cas). Personnellement, si je sais il n'y a pas plusieurs maxima (ou ne se soucient pas de faire), j'ai souvent tendance vers le null-gauche-auto-jointure de la méthode, je vais poster comme personne ne l'a encore:
```
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;
```

Classique de SQL-92 (pas à l'aide de l'OLAP opérations utilisées par Quassnoi), alors vous pouvez utiliser:

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;

(Case non cochée syntaxe; suppose que votre table est 't'.)

La sous-requête dans la clause from identifie le maximum de la valeur du signal pour chaque id; la jointure combine qu'avec les données correspondantes de lignes de la table principale.

NB: si il y a plusieurs entrées pour un code spécifique qui ont tous le même signal de force et que la force est le MAX(), puis vous obtiendrez plusieurs lignes de sortie pour cet ID.

Testé contre IBM Informix Dynamic Server 11.50.FC3 en cours d'exécution sur 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

J'ai nommé le tableau Signal_Info pour ce test, mais il semble produire de la bonne réponse.
Cela montre seulement qu'il y a au moins un SGBD qui prend en charge la notation. Cependant, je suis un peu surpris de voir que MS SQL Server ne prend pas en - quelle version utilisez-vous?

Il ne cesse jamais de me surprendre, à quelle fréquence SQL des questions sont posées sans les noms de table.


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

Ce retour de ligne, même si il y a des doublons de MAX(signal) pour un ID.

Ayant un index sur (id, signal) permettra d'améliorer grandement cette requête.

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

Nous pouvons faire à l'aide de l'auto rejoindre

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 vous pouvez également utiliser la requête suivante

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;

SÉLECTIONNEZ * à PARTIR de StatusTable 
Dans lequel le Signal ( 
SÉLECTIONNEZ A. maxSignal DE 
( 
SELECT ID, MAX(Signal) maxSignal 
DE StatusTable 
GROUP BY ID 
) EN TANT QUE 
);

Vous devez vous connecter pour publier un commentaire.