probleme avec distinct

Bonjour, j'ai un soucis avec un requette,

select e.ID, r.DEBUT from EVT as e, REP as r where e.ID = r.EVT_ID and e.ID in (1119,1118, 1117) order by r.DEBUT asc;

Citation:

---

+------+---------------------+
| ID | DEBUT |
+------+---------------------+
| 1119 | 2005-08-20 08:00:00 |
| 1117 | 2005-08-29 19:00:00 |
| 1118 | 2005-08-31 19:00:00 |
| 1117 | 2005-09-26 19:00:00 |
| 1118 | 2005-09-28 19:00:00 |
| 1118 | 2005-10-26 19:00:00 |
| 1117 | 2005-10-31 19:00:00 |
| 1117 | 2005-11-28 19:00:00 |
| 1118 | 2005-11-30 19:00:00 |
| 1117 | 2005-12-26 19:00:00 |
| 1118 | 2005-12-28 19:00:00 |
+------+---------------------+

---

ca, ok, par contre si je met :
select distinct e.ID from EVT as e, REP as r where e.ID = r.EVT_ID and e.ID in (1119,1118, 1117) order by r.DEBUT asc;

je voudrais que les premiers unique, soit : 1119, 1117, 1118
j'obtiens :

Citation:

---

+------+
| ID |
+------+
| 1119 |
| 1118 |
| 1117 |
+------+

---

Comment cela se fait il ?

Tu as oublié dans ton SELECT de préciser la date.

ben oui, justement, jene veux que les identifiant

et si je met :
select distinct e.ID, r.DEBUT from EVT as e, REP as r where e.ID = r.EVT_ID and e.ID in (1119,1118, 1117) order by r.DEBUT asc;

j'obtient le meme resultat que sans le distinct

Hmm je ne comprend pas trop le problème au final, peux-tu formaliser ce que tu souhaites obtenir ? Car forcément en faisant un distinct sur deux champs qui sont toujours uniques tu vas récupérer la même chose..

En faisait un distinct sur l'id, tu ne récupèreras pas les doublons, c'est à ça que sert distinct.. Que veux-tu faire exactement ?

Si tu ne veux que les identifiants :

Code:

---

1 2 3

select distinct e.ID from EVT as e where e.ID in (1119,1118, 1117)

---

mais je ne vois pas trop l'intérêt.

Si tu veux tes 3 lignes avec une date, il faut définir laquelle tu veux, par exemple :

Code:

---

1 2 3 4 5

SELECT e.ID, MAX(r.DEBUT) FROM EVT AS e, REP AS r WHERE e.ID = r.EVT_ID AND e.ID in (1119,1118, 1117) GROUP BY e.ID

---

et pourquoi pas

Code:

---

1 2 3 4

SELECT EVT_ID, MAX(DEBUT) FROM REP WHERE EVT_ID in (1119,1118, 1117) GROUP BY EVT_ID

---

Ce que je veux, c'est les identifiant des evenements par ordre de premiere apparition

Code:

---

1 2 3 4 5

SELECT EVT_ID FROM REP WHERE EVT_ID in (1119,1118, 1117) GROUP BY EVT_ID ORDER BY MIN(DEBUT)

---

ca fait pareil ca,
1119, 1118, 1117

[edit]
Autant pour moi, j'avais pas vu la fin at avait laissé "order by r.DEBUT asc;" : ca donne :

Citation:

---

ERROR 1111: Invalid use of group function

---

Postes la requête que tu exécutes ; quel moteur utilises-tu, quelle version ?

select EVENEMENT_ID from REPRESENTATION where EVENEMENT_ID in (1119,1118, 1117) group by EVENEMMENT_ID order by MIN(DEBUT);

celle la n'est pas bonne car debut fait parti de la table evenement


version de mysql ?
sous linux, je sais pas comment on l'obtient

pourquoi ne pas faire "FROM EVENEMENTS" ?

en fait, je peux me limiter a representation,

select r.EVENEMENT_ID, r.DEBUT from REPRESENTATION as r where r.EVENEMENT_ID in (1119,1118, 1117) order by r.DEBUT asc;

Citation:

---

+--------------+---------------------+
| EVENEMENT_ID | DEBUT |
+--------------+---------------------+
| 1119 | 2005-08-20 08:00:00 |
| 1117 | 2005-08-29 19:00:00 |
| 1118 | 2005-08-31 19:00:00 |
| 1117 | 2005-09-26 19:00:00 |
| 1118 | 2005-09-28 19:00:00 |
| 1118 | 2005-10-26 19:00:00 |
| 1117 | 2005-10-31 19:00:00 |
| 1117 | 2005-11-28 19:00:00 |
| 1118 | 2005-11-30 19:00:00 |
| 1117 | 2005-12-26 19:00:00 |
| 1118 | 2005-12-28 19:00:00 |
+--------------+---------------------+

---

select distinct r.EVENEMENT_ID from REPRESENTATION as r where r.EVENEMENT_ID in (1119,1118, 1117) order by r.DEBUT asc;

Citation:

---

+--------------+
| EVENEMENT_ID |
+--------------+
| 1119 |
| 1118 |
| 1117 |
+--------------+

---

select EVENEMENT_ID from REPRESENTATION where EVENEMENT_ID in (1119,1118, 1117) group by EVENEMMENT_ID order by MIN(DEBUT);

Citation:

---

ERROR 1111: Invalid use of group function

---

Je ne connais pas mySQL, mais sous ORACLE et sous SQLServer, cela marche parfaitement, je viens de faire les tests.

snif ... :(

Par curiosité, est-ce que tu peux essayer :

Code:

---

1 2 3 4 5

SELECT EVT_ID, MIN(DEBUT) as Deb FROM REP WHERE EVT_ID in (1119,1118, 1117) GROUP BY EVT_ID ORDER BY Deb

---

ou encore

Code:

---

1 2 3 4 5

SELECT EVT_ID, MIN(DEBUT) FROM REP WHERE EVT_ID in (1119,1118, 1117) GROUP BY EVT_ID ORDER BY 2

---

mysql> select EVENEMENT_ID, MIN(DEBUT) ad Deb FROM REPRESENTATION WHERE EVENEMENT_ID in (1119,1118,1117) GROUP BY EVENEMENT_ID ORDER BY Deb;

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Deb FROM REPRESENTATION WHERE EVENEMENT_ID in (1119,1118,1117)

mysql> select EVENEMENT_ID, MIN(DEBUT) ad Deb FROM REPRESENTATION WHERE EVENEMENT_ID in (1119,1118,1117) GROUP BY EVENEMENT_ID ORDER BY 2;

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Deb FROM REPRESENTATION WHERE EVENEMENT_ID in (1119,1118,1117)

Citation:

---

Envoyé par firejocker

mysql> select EVENEMENT_ID, MIN(DEBUT) ad Deb FROM REPRESENTATION WHERE EVENEMENT_ID in (1119,1118,1117) GROUP BY EVENEMENT_ID ORDER BY Deb;

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Deb FROM REPRESENTATION WHERE EVENEMENT_ID in (1119,1118,1117)

mysql> select EVENEMENT_ID, MIN(DEBUT) ad Deb FROM REPRESENTATION WHERE EVENEMENT_ID in (1119,1118,1117) GROUP BY EVENEMENT_ID ORDER BY 2;

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Deb FROM REPRESENTATION WHERE EVENEMENT_ID in (1119,1118,1117)

---

ad au lieu de as !

well one !!!

Citation:

---

mysql> select EVENEMENT_ID, MIN(DEBUT) as Deb FROM REPRESENTATION WHERE EVENEMENT_ID in (1119,1118,1117) GROUP BY EVENEMENT_ID ORDER BY Deb;
+--------------+---------------------+
| EVENEMENT_ID | Deb |
+--------------+---------------------+
| 1119 | 2005-08-20 08:00:00 |
| 1117 | 2005-08-29 19:00:00 |
| 1118 | 2005-08-31 19:00:00 |
+--------------+---------------------+
3 rows in set (0.00 sec)

mysql> select EVENEMENT_ID, MIN(DEBUT) as Deb FROM REPRESENTATION WHERE EVENEMENT_ID in (1119,1118,1117) GROUP BY EVENEMENT_ID ORDER BY 2;
+--------------+---------------------+
| EVENEMENT_ID | Deb |
+--------------+---------------------+
| 1119 | 2005-08-20 08:00:00 |
| 1117 | 2005-08-29 19:00:00 |
| 1118 | 2005-08-31 19:00:00 |
+--------------+---------------------+
3 rows in set (0.00 sec)

---