Discussion :

[_tibo]

Salut

Dans un système qui gère des exercices en ligne, des personnes répondent à un exercice et enregistrent leurs résultats. Il y a bien sûr plusieurs utilisateurs (personnes), plusieurs exercices et chacun peut répondre zero, une ou plusieurs fois à un exercice.

Je voudrais obtenir la liste de tous les derniers résultats (colonne data ci-dessous) pour chaque utilisateur et exercice - c'est à dire le résultat le plus récent donné par une personne à un exercice.

Je n'ai pas trouvé de manière élégante d'écrire cette requête. J'ai beau me creuser la tête, je n'arrive pas à trouver une requête sans boucle (procédure PL/pgSQL) qui résolve mon problème - et je suis persuadé qu'il en existe une, voilà pourquoi je fais appel à votre expérience.

J'ai une table qui ressemble à ceci (définition SQL plus bas) pour deux personnes et deux exercices:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
 id | person | exercise |         createdon          |      data
----+--------+----------+----------------------------+-----------------
  1 |      1 |        1 | 2009-02-17 16:55:21.328+01 | user1 ex1 data1
  2 |      1 |        1 | 2009-02-17 16:55:22.125+01 | user1 ex1 data2
  3 |      1 |        1 | 2009-02-17 16:55:22.906+01 | user1 ex1 data3
  4 |      1 |        2 | 2009-02-17 16:55:23.687+01 | user1 ex2 data1
  5 |      1 |        2 | 2009-02-17 16:55:24.453+01 | user1 ex2 data2
  6 |      2 |        1 | 2009-02-17 16:55:25.234+01 | user2 ex1 data1
  7 |      2 |        1 | 2009-02-17 16:55:26.015+01 | user2 ex1 data2
  8 |      2 |        2 | 2009-02-17 16:55:26.812+01 | user2 ex2 data1

Je voudrais donc que la requête me retourne les lignes 3, 5, 7 et 8.

Je vois bien comment faire pour trouver le résultat le plus récent pour une personne donnée et un exercice donné

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
SELECT * FROM Result WHERE Person=<persid> AND Exercise=<exid> ORDER BY CreatedOn DESC LIMIT 1;

mais j'aimerais fournir une vue au professeur pour qu'il puisse d'un coup d'oeil voir tous les résultats "actuels" sans être encombré par les éventuels plus anciens.

Merci beaucoup d'avance

Thibault

PS: voici le code SQL pour générer la table de l'exemple:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
CREATE TABLE test (
	ID serial UNIQUE NOT NULL,
	Person int NOT NULL,
	--FOREIGN KEY (Person) REFERENCES Person(ID),
	Exercise int NOT NULL,
	--FOREIGN KEY (Exercise) REFERENCES Exercise(ID),
	CreatedOn timestamptz NOT NULL DEFAULT now(),
	Data text NOT NULL
);
 
INSERT INTO test(Person,Exercise,Data) VALUES (1,1,'user1 ex1 data1');
INSERT INTO test(Person,Exercise,Data) VALUES (1,1,'user1 ex1 data2');
INSERT INTO test(Person,Exercise,Data) VALUES (1,1,'user1 ex1 data3');
INSERT INTO test(Person,Exercise,Data) VALUES (1,2,'user1 ex2 data1');
INSERT INTO test(Person,Exercise,Data) VALUES (1,2,'user1 ex2 data2');
INSERT INTO test(Person,Exercise,Data) VALUES (2,1,'user2 ex1 data1');
INSERT INTO test(Person,Exercise,Data) VALUES (2,1,'user2 ex1 data2');
INSERT INTO test(Person,Exercise,Data) VALUES (2,2,'user2 ex2 data1');

[GoLDoZ]

Bonjour.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT   person, exercise, MAX (createdon)
    FROM test
GROUP BY person, exercise

[_tibo]

Bonjour.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT   person, exercise, MAX (createdon)
    FROM test
GROUP BY person, exercise

Merci pour la proposition, mais on n'obtient "que" les personnes et les exercices de cette manière, pas l'ID correspondant ni la valeur de DATA.

En ajoutant une contrainte

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

UNIQUE(Person,Exercise,CreatedOn)

qui ne pose pas trop de problème - on ne pourra pas insérer deux résultats pour un même utilisateur/exercice dans une même transaction - j'en arrive à la requête suivante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
SELECT Test.ID,Test.Person,Test.Exercise,Test.CreatedOn,Test.Data
FROM 
    (SELECT Person,Exercise,max(CreatedOn)
        FROM Test
        GROUP BY Person,Exercise) AS Foo,
    Test
WHERE Test.Person = Foo.Person
AND Test.Exercise = Foo.Exercise
AND Test.CreatedOn = Foo.max

Merci pour le tuyau en tout cas.

Et si quelqu'un a une recette magique pour me passer de la sous-requête et de la contrainte d'unicité, je suis preneur

[GoLDoZ]

Si la colonne createdon contient toujours la sysdate, la contrainte d'unicité est inutile.
A part cela, j'utilise souvent la solution que vous proposez.