mysql - values - sql group by




Comment puis-je sélectionner des lignes avec MAX(valeur de colonne), DISTINCT par une autre colonne dans SQL? (12)

@Michae La réponse acceptée fonctionnera bien dans la plupart des cas, mais elle échoue pour un pour comme ci-dessous.

Dans le cas où il y avait 2 lignes ayant HomeID et Datetime même la requête retournera les deux lignes, pas HomeID distinct tel que requis, pour cela ajouter Distinct dans la requête comme ci-dessous.

SELECT DISTINCT tt.home  , tt.MaxDateTime
FROM topten tt
INNER JOIN
    (SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home) groupedtt 
ON tt.home = groupedtt.home 
AND tt.datetime = groupedtt.MaxDateTime

Ma table est:

id  home  datetime     player   resource
---|-----|------------|--------|---------
1  | 10  | 04/03/2009 | john   | 399 
2  | 11  | 04/03/2009 | juliet | 244
5  | 12  | 04/03/2009 | borat  | 555
3  | 10  | 03/03/2009 | john   | 300
4  | 11  | 03/03/2009 | juliet | 200
6  | 12  | 03/03/2009 | borat  | 500
7  | 13  | 24/12/2008 | borat  | 600
8  | 13  | 01/01/2009 | borat  | 700

J'ai besoin de sélectionner chaque home distincte contenant la valeur maximale de datetime .

Le résultat serait:

id  home  datetime     player   resource 
---|-----|------------|--------|---------
1  | 10  | 04/03/2009 | john   | 399
2  | 11  | 04/03/2009 | juliet | 244
5  | 12  | 04/03/2009 | borat  | 555
8  | 13  | 01/01/2009 | borat  | 700

J'ai essayé:

-- 1 ..by the MySQL manual: 

SELECT DISTINCT
  home,
  id,
  datetime AS dt,
  player,
  resource
FROM topten t1
WHERE datetime = (SELECT
  MAX(t2.datetime)
FROM topten t2
GROUP BY home)
GROUP BY datetime
ORDER BY datetime DESC

Ne fonctionne pas Result-set a 130 lignes bien que la base de données en contient 187. Le résultat inclut quelques doublons de home .

-- 2 ..join

SELECT
  s1.id,
  s1.home,
  s1.datetime,
  s1.player,
  s1.resource
FROM topten s1
JOIN (SELECT
  id,
  MAX(datetime) AS dt
FROM topten
GROUP BY id) AS s2
  ON s1.id = s2.id
ORDER BY datetime 

Nan. Donne tous les enregistrements.

-- 3 ..something exotic: 

Avec divers résultats.


Cela fonctionne sur Oracle:

with table_max as(
  select id
       , home
       , datetime
       , player
       , resource
       , max(home) over (partition by home) maxhome
    from table  
)
select id
     , home
     , datetime
     , player
     , resource
  from table_max
 where home = maxhome

Depuis que les gens semblent continuer à courir dans ce fil (la date de commentaire varie de 1,5 année) n'est pas beaucoup plus simple:

SELECT * FROM (SELECT * FROM topten ORDER BY datetime DESC) tmp GROUP BY home

Aucune fonction d'agrégation nécessaire ...

À votre santé.


Essaye ça

select * from mytable a join
(select home, max(datetime) datetime
from mytable
group by home) b
 on a.home = b.home and a.datetime = b.datetime

Cordialement K


Ici va la version T-SQL :

-- Test data
DECLARE @TestTable TABLE (id INT, home INT, date DATETIME, 
  player VARCHAR(20), resource INT)
INSERT INTO @TestTable
SELECT 1, 10, '2009-03-04', 'john', 399 UNION
SELECT 2, 11, '2009-03-04', 'juliet', 244 UNION
SELECT 5, 12, '2009-03-04', 'borat', 555 UNION
SELECT 3, 10, '2009-03-03', 'john', 300 UNION
SELECT 4, 11, '2009-03-03', 'juliet', 200 UNION
SELECT 6, 12, '2009-03-03', 'borat', 500 UNION
SELECT 7, 13, '2008-12-24', 'borat', 600 UNION
SELECT 8, 13, '2009-01-01', 'borat', 700

-- Answer
SELECT id, home, date, player, resource 
FROM (SELECT id, home, date, player, resource, 
    RANK() OVER (PARTITION BY home ORDER BY date DESC) N
    FROM @TestTable
)M WHERE N = 1

-- and if you really want only home with max date
SELECT T.id, T.home, T.date, T.player, T.resource 
    FROM @TestTable T
INNER JOIN 
(   SELECT TI.id, TI.home, TI.date, 
        RANK() OVER (PARTITION BY TI.home ORDER BY TI.date) N
    FROM @TestTable TI
    WHERE TI.date IN (SELECT MAX(TM.date) FROM @TestTable TM)
)TJ ON TJ.N = 1 AND T.id = TJ.id

MODIFIER
Malheureusement, il n'y a pas de fonction RANK () OVER dans MySQL.
Mais il peut être émulé, voir Émulation des fonctions analytiques (classement AKA) avec MySQL .
Donc, ceci est la version de MySQL :

SELECT id, home, date, player, resource 
FROM TestTable AS t1 
WHERE 
    (SELECT COUNT(*) 
            FROM TestTable AS t2 
            WHERE t2.home = t1.home AND t2.date > t1.date
    ) = 0

Je pense que cela vous donnera le résultat souhaité:

SELECT   home, MAX(datetime)
FROM     my_table
GROUP BY home

MAIS si vous avez besoin d'autres colonnes, faites juste une jointure avec la table originale (vérifiez Michael La Voie réponse de Michael La Voie )

Meilleures salutations.


Pourquoi ne pas utiliser: SELECT home, MAX (date / heure) AS MaxDateTime, lecteur, ressource FROM topten GROUP BY home Ai-je raté quelque chose?


Une autre façon de gt la ligne la plus récente par groupe en utilisant une sous-requête qui calcule fondamentalement un rang pour chaque ligne par groupe, puis filtrez vos lignes les plus récentes comme avec rank = 1

select a.*
from topten a
where (
  select count(*)
  from topten b
  where a.home = b.home
  and a.`datetime` < b.`datetime`
) +1 = 1

DEMO

Voici la démo visuelle pour le rang non pour chaque rangée pour une meilleure compréhension

En lisant certains commentaires, qu'en est-il s'il y a deux lignes qui ont les mêmes valeurs de champs 'home' et 'datetime'?

La requête ci-dessus échouera et retournera plus de 1 lignes pour la situation ci-dessus. Pour masquer cette situation, il sera nécessaire d'avoir un autre critère / paramètre / colonne pour décider quelle rangée doit être prise qui correspond à la situation ci-dessus. En regardant un ensemble de données, je suppose qu'il y a un id colonne de clé primaire qui devrait être réglé sur l'incrémentation automatique. Nous pouvons donc utiliser cette colonne pour sélectionner la ligne la plus récente en modifiant la même requête à l'aide de l'instruction CASE comme

select a.*
from topten a
where (
  select count(*)
  from topten b
  where a.home = b.home
  and  case 
       when a.`datetime` = b.`datetime`
       then a.id < b.id
       else a.`datetime` < b.`datetime`
       end
) + 1 = 1

DEMO

La requête ci-dessus sélectionnera la ligne ayant le plus grand identifiant parmi les mêmes valeurs de datetime

démo visuelle pour le rang non pour chaque rangée


Vous êtes si proche! Tout ce que vous devez faire est de sélectionner à la fois la maison et son heure de date maximale, puis rejoignez la table d'en topten sur les deux champs:

SELECT tt.*
FROM topten tt
INNER JOIN
    (SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home) groupedtt 
ON tt.home = groupedtt.home 
AND tt.datetime = groupedtt.MaxDateTime

Vous pouvez également essayer celui-ci et pour les grandes tables, les performances des requêtes seront meilleures. Cela fonctionne quand il n'y a pas plus de deux enregistrements pour chaque maison et leurs dates sont différentes. Une meilleure requête MySQL générale est celle de Michael La Voie ci-dessus.

SELECT t1.id, t1.home, t1.date, t1.player, t1.resource
FROM   t_scores_1 t1 
INNER JOIN t_scores_1 t2
   ON t1.home = t2.home
WHERE t1.date > t2.date

Ou dans le cas de Postgres ou ceux qui fournissent des fonctions analytiques essayer

SELECT t.* FROM 
(SELECT t1.id, t1.home, t1.date, t1.player, t1.resource
  , row_number() over (partition by t1.home order by t1.date desc) rw
 FROM   topten t1 
 INNER JOIN topten t2
   ON t1.home = t2.home
 WHERE t1.date > t2.date 
) t
WHERE t.rw = 1

SELECT  tt.*
FROM    TestTable tt 
INNER JOIN 
        (
        SELECT  coord, MAX(datetime) AS MaxDateTime 
        FROM    rapsa 
        GROUP BY
                krd 
        ) groupedtt
ON      tt.coord = groupedtt.coord
        AND tt.datetime = groupedtt.MaxDateTime

SELECT c1, c2, c3, c4, c5 FROM table1 WHERE c3 = (select max(c3) from table)

SELECT * FROM table1 WHERE c3 = (select max(c3) from table1)




greatest-n-per-group