afficher l'étudiant le plus agé de chaque classe ?

-- les tables
CREATE TABLE t_etudiant
(
etudiant_id INTEGER PRIMARY KEY NOT NULL,
date_naissance DATE NOT NULL
);

CREATE TABLE t_classe
(
classe_id INTEGER PRIMARY KEY NOT NULL,
classe_nom VARCHAR(200)
);

CREATE TABLE t_etudiant_classes
(
classe_id INTEGER,
etudiant_id INTEGER,
PRIMARY KEY (etudiant_id, classe_id)
);

-- un jeu d'essai :

INSERT INTO t_etudiant
VALUES (1, '1900-01-01'),
(2, '1910-01-01'),
(3, '1920-01-01'),
(4, '1910-01-01'),
(5, '1930-01-01'),
(6, '1940-01-01'),
(7, '1950-01-01');

INSERT INTO t_classe
VALUES (1, 'chant'), (2, 'musique'), (3, 'danse');

INSERT INTO t_etudiant_classes
VALUES (1, 2), (1, 4), (1, 7),
(2, 1), (2, 4), (2, 6),
(3, 3), (3, 5), (3, 6);

-- la solution :

WITH T
AS
(
SELECT classe_id, MIN(date_naissance) AS DN_max
FROM t_etudiant_classes AS EC
INNER JOIN t_etudiant AS E
ON EC.etudiant_id = E.etudiant_id
GROUP BY classe_id
)
SELECT classe_id, E.*
FROM T
INNER JOIN t_etudiant AS E
ON T.DN_max = E.date_naissance
ORDER BY classe_id;

-- le résultat :

classe_id etudiant_id date_naissance
----------- ----------- --------------
1 2 1910-01-01
1 4 1910-01-01
2 1 1900-01-01
3 3 1920-01-01

Pour apprendre le SQL;, mon site, comme mon bouquin est là pour vous aider !

A +


--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************



Pierre a écrit :

bonjour

je dois afficher pour chaque classe l'étudiant le plus agé, mais un étudiant
peut être dans plusieurs classes.

j'ai les tables existantes comme ca :

t_etudiant
(
etudiant_id INTEGER PRIMARY KEY NOT NULL,
date_naissance DATE NOT NULL
);

t_classe
(
classe_id INTEGER PRIMARY KEY NOT NULL,
classe_nom VARCHAR(200)
);

t_etudiant_classes
(
etudiant_id INTEGER,
classe_id INTEGER,
PRIMARY KEY (etudiant_id, classe_id)
);

j'ai fait un premier essai dans un GROUP BY où je faisais un LIMIT 1 et un
ORDER BY,
mais cette requete ne pouvait pas fonctionner correctement.

j'ai alors essayé de faire une auto-jointure, mais le résultat n'est pas
bon.
car au lieu de filtrer, j'ai réalisé un produit cartésien !

voici mon essai :

SELECT t_etudiant_classes.classe_id,
t_etudiant_classes.etudiant_id
FROM t_etudiant_classes
INNER JOIN t_etudiant ON t_etudiant.etudiant_id > t_etudiant_classes.etudiant_id
INNER JOIN (
SELECT classe_id,
etudiant_id,
t_etudiant.date_ajout
FROM t_etudiant_classes
INNER JOIN t_etudiant ON t_etudiant.etudiant_id > t_etudiant_classes.etudiant_id
) AS autres_etudiants ON (
t_etudiant_classes.classe_id > autres_etudiants.classe_id
AND t_etudiant.date_naissance > > autres_etudiants.date_naissance
);


suis-je sur une mauvaise piste ?
merci pour vos explications !

SQLpro a écrit :

-- les tables
CREATE TABLE t_etudiant
(
etudiant_id INTEGER PRIMARY KEY NOT NULL,
date_naissance DATE NOT NULL
);

CREATE TABLE t_classe
(
classe_id INTEGER PRIMARY KEY NOT NULL,
classe_nom VARCHAR(200)
);

CREATE TABLE t_etudiant_classes
(
classe_id INTEGER,
etudiant_id INTEGER,
PRIMARY KEY (etudiant_id, classe_id)
);

-- un jeu d'essai :

INSERT INTO t_etudiant
VALUES (1, '1900-01-01'),
(2, '1910-01-01'),
(3, '1920-01-01'),
(4, '1910-01-01'),
(5, '1930-01-01'),
(6, '1940-01-01'),
(7, '1950-01-01');

INSERT INTO t_classe
VALUES (1, 'chant'), (2, 'musique'), (3, 'danse');

INSERT INTO t_etudiant_classes
VALUES (1, 2), (1, 4), (1, 7),
(2, 1), (2, 4), (2, 6),
(3, 3), (3, 5), (3, 6);

-- la solution :

WITH T
AS
(
SELECT classe_id, MIN(date_naissance) AS DN_max
FROM t_etudiant_classes AS EC
INNER JOIN t_etudiant AS E
ON EC.etudiant_id = E.etudiant_id
GROUP BY classe_id
)
SELECT classe_id, E.*
FROM T
INNER JOIN t_etudiant AS E
ON T.DN_max = E.date_naissance
ORDER BY classe_id;

-- le résultat :

classe_id etudiant_id date_naissance
----------- ----------- --------------
1 2 1910-01-01
1 4 1910-01-01
2 1 1900-01-01
3 3 1920-01-01

Pour apprendre le SQL;, mon site, comme mon bouquin est là pour vous
aider !

A +

avec le Gay Motard Club ont est jamais trop aidé :-) sacré Fred