Requête SQL pour obtenir les valeurs d'une colonne qui correspondent à la valeur MAX d'une autre colonne ?

Question

Ok, voici ma question :

SELECT
  video_category,
  video_url,
  video_date,
  video_title,
  short_description,
  MAX(video_id) 
FROM
  videos
GROUP BY
  video_category

Lorsqu'il extrait les données, j'obtiens la bonne ligne pour l'identifiant vidéo, mais il extrait la première ligne de chaque catégorie pour les autres. Ainsi, lorsque j'obtiens le résultat maximal pour l'identifiant vidéo de la catégorie 1, j'obtiens l'identifiant maximal, mais la première ligne de la table pour l'URL, la date, le titre et la description.

Comment puis-je faire en sorte qu'il tire les autres colonnes qui correspondent au résultat de l'ID maximum ?

Edit : Corrigé.

SELECT
    *
FROM
    videos
WHERE
    video_id IN
    (
        SELECT
            DISTINCT
            MAX(video_id)
        FROM
            videos
        GROUP BY
            video_category
    ) 
ORDER BY
    video_category ASC

Answer 1

J'essaierais quelque chose comme ça :

SELECT
   s.video_id
   ,s.video_category
   ,s.video_url
   ,s.video_date
   ,s.video_title
   ,short_description
FROM videos s
   JOIN (SELECT MAX(video_id) AS id FROM videos GROUP BY video_category) max
      ON s.video_id = max.id

ce qui est bien plus rapide que votre propre solution

Answer 2

J'ai récemment publié une nouvelle technique pour traiter ce type de problème dans MySQL.

RÉDUCTION SCALAIRE-AGRÉGÉE

La réduction scalaire-agrégée est de loin l'approche la plus performante et la méthode la plus simple (en termes de moteur de base de données) pour y parvenir, car elle ne nécessite ni jointures, ni sous-requêtes, ni CTE.

Pour votre requête, cela ressemblerait à quelque chose comme ceci :

SELECT
  video_category,
  MAX(video_id) AS video_id,
  SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_url)), 12) AS video_url,
  SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_date)), 12) AS video_date,
  SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_title)), 12) AS video_title,
  SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), short_description)), 12) AS short_description
FROM
  videos
GROUP BY
  video_category

La combinaison des fonctions scalaires et agrégées permet d'obtenir les résultats suivants :

1. LPADs l'identifiant corrélé intra-agrégat pour permettre une comparaison correcte des chaînes de caractères (par exemple, "0009" et "0025" seront correctement classés). J'utilise ici un LPADDING de 11 caractères en supposant une clé primaire INT. Si vous utilisez un BIGINT, vous voudrez augmenter cette valeur pour prendre en charge l'ordinalité de votre table. Si vous effectuez une comparaison sur un champ DATETIME (longueur fixe), aucun remplissage n'est nécessaire.
2. CONCAT l'identifiant padded avec la colonne de sortie (ainsi vous obtenez "00000000009myvalue" contre "0000000025othervalue")
3. MAX l'ensemble des agrégats, ce qui donnera "00000000025othervalue" comme gagnant.
4. SUBSTRING le résultat, ce qui tronquera la partie identifiant comparé, ne laissant que la valeur.

Si vous voulez récupérer des valeurs dans des types autres que CHAR, vous devrez peut-être effectuer un CAST supplémentaire sur la sortie, par exemple si vous voulez que video_date pour être un DATETIME :

CAST(SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_date)), 12) AS DATETIME)

Un autre avantage de cette méthode par rapport à la méthode d'auto-jonction est que vous pouvez combiner d'autres données agrégées (pas seulement les dernières valeurs), ou même combiner le premier ET le dernier élément dans la même requête, par ex.

SELECT
    -- Overall totals
    video_category,
    COUNT(1) AS videos_in_category,
    DATEDIFF(MAX(video_date), MIN(video_date)) AS timespan,

    -- Last video details
    MAX(video_id) AS last_video_id,
    SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_url)), 12) AS last_video_url,
    ...

    -- First video details
    MIN(video_id) AS first_video_id,
    SUBSTRING(MIN(CONCAT(LPAD(video_id, 11, '0'), video_url)), 12) AS first_video_url,
    ...

    -- And so on

Pour plus de détails expliquant les avantages de cette méthode par rapport aux autres méthodes plus anciennes, mon article de blog complet est ici : https://www.stevenmoseley.com/blog/tech/high-performance-sql-correlated-scalar-aggregate-reduction-queries

Answer 3

Voici une solution plus générale (qui gère les doublons)

CREATE TABLE test(
  i INTEGER,
  c INTEGER,
  v INTEGER
);

insert into test(i, c, v)
values
(3, 1, 1),
(3, 2, 2),
(3, 3, 3),
(4, 2, 4),
(4, 3, 5),
(4, 4, 6),
(5, 3, 7),
(5, 4, 8),
(5, 5, 9),
(6, 4, 10),
(6, 5, 11),
(6, 6, 12);

SELECT t.c, t.v
FROM test t
JOIN (SELECT test.c, max(i) as mi FROM test GROUP BY c) j ON
  t.i = j.mi AND
  t.c  = j.c
ORDER BY c;

Answer 4

SELECT video_category,video_url,video_date,video_title,short_description,video_id FROM videos t1 où video_id dans (SELECT max(video_id) FROM videos t2 WHERE t1.video_category=t2.video_category ) ;

Veuillez fournir vos enregistrements d'entrée et de sortie afin qu'il puisse être compris correctement et testé.