classements multiples en 1 requete uniquement

**warpyou** · 29/05/2006, 17h09

Donc voilà un petit exemple très simplifié pour vous expliquer mon problème :
my_table
id | prenom | age
-----------------
1 | paul | 45
2 | louis | 32
3 | rene | 68
4 | ciryl | 24
5 | jean | 30

Je voudrais savoir si en une seule requête je peux récupérer les 2 personnes les plus agées (donc par classement en ordre numérique décroissant sur la colonne age j'obtiens rene puis paul) et obtenir le résultat de ma requète classé sur leur prenom par ordre alphabétique croissant (soit au final paul puis rene).
J'ai l'impression que ce n'est pas possible en une seule requete, est-ce que je me trompe ? (à ce moment là, j'effectue mon classement alphabétique sur les prenoms en php par exemple)

P.S: la colonne prenom est en UNIQUE

**Alexandre T** · 29/05/2006, 17h39

Envoyé par warpyou

J'ai l'impression que ce n'est pas possible en une seule requete, est-ce que je me trompe ?

Ca dépend de votre version de mysql !

**warpyou** · 29/05/2006, 18h00

là je tourne sur la 3.23.54-nt, mais ça me pose aucun soucis d'upgrader à la dernière.

**chasse** · 29/05/2006, 18h15

Et avec qqch de ce style, pour ordrer par age puis par nom...

Code :

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

1
2
3
 
Select * from table order by Nom where id =
(Select id from table order by age)

**warpyou** · 29/05/2006, 18h23

Envoyé par chasse

Et avec qqch de ce style, pour ordrer par age puis par nom...

Code :

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

1
2
3
 
Select * from table order by Nom where id =
(Select id from table order by age)

J'aurai du préciser mais je cherchais une possibilité sans subselect qui pour moi s'apparente à une double requete (dans mon projet, la rapidité des requettes SQL est cruciale)

**Alexandre T** · 29/05/2006, 19h26

Voici la solution en une seule requête, mais attention, elle ne respecte pas les standards de la norme SQL. Les prénoms ne seront classés par ordre alphabétique que si les deux personnes les plus agées ont le même âge.

Code :

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

1
2
3
4
5
 
SELECT ID, PRENOM, AGE
  FROM MY_TABLE
 ORDER BY AGE DESC, PRENOM ASC
 LIMIT 2

Juste une question pour la suite, votre requête finale, donc celle non simplifiée, remontera-t-elle que 2 valeurs ou peut-elle en remonter plus.

**warpyou** · 29/05/2006, 21h00

Envoyé par Alexandre T

Code :

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

1
2
3
4
5
 
SELECT ID, PRENOM, AGE
  FROM MY_TABLE
 ORDER BY AGE DESC, PRENOM ASC
 LIMIT 2

Oui, j'ais essayé ça, mais le problème c'est que age n'a pas forcément la même valeur donc ça ne marche pas (de même dans l'autre sens avec des prenoms identiques) et en effet, sur ma requete complexe LIMIT peut être aussi bien 10 que 100. Je pense qu'il ne reste plus qu' à me rabattre sur le classement alphabétique avec PHP après la requète SQL.

**Alexandre T** · 30/05/2006, 09h21

Si le limit ne dépasse pas 100, alors oui, PHP le gérera très bien. Sinon hier soir j'ai trouvé une théorie pour que cela marche. C'est basé sur la méthodologie que j'utilise en MSSQL SERVER 2000 où les valeurs comme numrow() ou rownum() n'existe pas. Je suis en train de rédiger la requête. Je devrais la poster entre midi et deux.

**Alexandre T** · 30/05/2006, 13h58

Méthode avec une auto jointure.

Code :

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

1
2
3
4
5
SELECT  s1.id,s1.prenom,s1.age, count(*) as rank
FROM  my_table s1,my_table s2
WHERE   s1.age <= s2.age
GROUP BY  s1.id,s1.prenom,s1.age
order by  prenom asc;

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
+------+--------+------+------+
| id   | prenom | age  | rank |
+------+--------+------+------+
|    4 | ciryl  |   24 |    5 |
|    5 | jean   |   30 |    4 |
|    2 | louis  |   32 |    3 |
|    1 | paul   |   45 |    2 |
|    3 | rene   |   68 |    1 |
+------+--------+------+------+
5 rows in set (0.02 sec)

Maintenant on ne veut que les x plus vieux. On pose x = 2 :

Code :

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

1
2
3
4
5
6
7
SELECT  s1.id,s1.prenom,s1.age, count(*) as rank
FROM  my_table s1,my_table s2
WHERE   s1.age <= s2.age
GROUP BY  s1.id,s1.prenom,s1.age
having rank <=2
order by  prenom asc;

Code :

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

1
2
3
4
5
6
7
8
 
+------+--------+------+------+
| id   | prenom | age  | rank |
+------+--------+------+------+
|    1 | paul   |   45 |    2 |
|    3 | rene   |   68 |    1 |
+------+--------+------+------+
2 rows in set (0.00 sec)

Voilà.

Variante plus propre :

Code :

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

1
2
3
4
5
6
SELECT  s1.id,s1.prenom,s1.age, count(*) as rank
FROM  my_table s1
INNER JOIN my_table s2 ON s1.age <= s2.age
GROUP BY  s1.id,s1.prenom,s1.age
having rank <=2
order by  prenom asc;

Code :

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

1
2
3
4
5
6
7
+------+--------+------+------+
| id   | prenom | age  | rank |
+------+--------+------+------+
|    1 | paul   |   45 |    2 |
|    3 | rene   |   68 |    1 |
+------+--------+------+------+
2 rows in set (0.00 sec)

Cette méthode respecte totalement la norme SQL, et n'utilise pas de mot clef comme LIMIT ou TOP !

Placez de bons index et le tour et jouer. De visu, je dirais un sur l'id (qui doit être la clef primaire, le second sur age.

Cordialement,

**warpyou** · 30/05/2006, 20h45

Alexandre T

Merci !

Je viens juste de l'intégrer et ça fonctionne à merveille, bien vu l'auto jointure.
Je vais faire un test de rapidité comparé au post traitement par PHP (j'ai simplifié mon code en exemple pour qu'il reste en corrélation avec l'exemple du post, si ça peut aider quelqu'un)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
<?php
...
$query = "SELECT s.* FROM my_table s ORDER BY s.age DESC LIMIT 100";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {<div style="margin-left:40px">$individu_list[] = $row;</div>}
function sortByName($a, $b)
{<div style="margin-left:40px">if ($a['prenom'] == $b['prenom']) return 0;
return ($a['prenom'] < $b['prenom']) ? -1 : 1;</div>}
usort($individu_list, 'sortByName');
...
?>

**warpyou** · 31/05/2006, 01h43

Je viens de faire quelques tests, il semble que le post classement alphabétique par PHP soit quand même plus rapide (grossièrement on divise presque par 2 le temps d'exécution pour retourner 50 valeurs sur une table d'environ 100). Il faudrait que je teste sur des valeurs bien plus grande pour voir à partir de quel nombre ta requète devient plus rapide.
En tout cas elle est plus "classe" que les 6 lignes de PHP qu'elle fait sauter dans mon scripte

Encore merci.

**Alexandre T** · 31/05/2006, 08h59

Je suis étonné de ces résultats. En même temps, si vous n'avez que 100 valeurs PHP les classera très rapidement. Si ce n'est déjà fait, placez un index sur l'id. Placez également un index sur le champ âge et un dernier sur prenom.

**warpyou** · 31/05/2006, 10h46

Je viens de refaire quelques tests à partir d'une table de 10000 entrées où prenom est une chaine texte aléatoire de 4 à 20 charactères (sans doublons de prenom) et age un chiffre aléatoire entre 1 et 100000.
Les champs sont bien indexés :
PRIMARY KEY (id)
UNIQUE KEY (prenom)
KEY (age)
les résultats sont sans appel, le post classement alphabétique en PHP est nettement plus rapide même sur un retour de 2500 valeurs sur 10000.
J'ai essayé ta requete directement sur MySQL sans passer par mon script PHP mais elle met un temps fou à s'exécuter :

Code :

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

1
2
3
4
5
6
7
 
SELECT  s1.id,s1.prenom,s1.age, count(*) as rank
FROM  my_table s1
INNER JOIN my_table s2 ON s1.age <= s2.age
GROUP BY  s1.id,s1.prenom,s1.age
having rank <=2500
order by  prenom asc;

**Alexandre T** · 31/05/2006, 11h08

Merci d'avoir fait ces tests de comparaison !

**Sivrît** · 31/05/2006, 15h55

Pour MySQL 5 il y a :

Code :

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

1
2
 
(SELECT * FROM my_table ORDER BY age LIMIT 2 ) ORDER BY nom;

Sinon, effectivement les requêtes imbriquées sont potentiellement lentes... mais elles peuvent être rapides suivant ce que l'on fait.

Par exemple :

Code :

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

1
2
 
SELECT * FROM (SELECT * FROM my_table ORDER BY age LIMIT 2) AS toto ORDER BY toto.nom;

prend autant de temps que la première que je cite et est infiniment plus rapide que l'utilisation d'une auto jointure (qui est une vraie ignominie en terme d'efficacité !)

**Alexandre T** · 31/05/2006, 16h08

Envoyé par Sivrît

Pour MySQL 5 il y a :

Code :

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

1
2
 
(SELECT * FROM my_table ORDER BY age LIMIT 2 ) ORDER BY nom;

Il faut tester avant de dire une bétise.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
mysql> (SELECT * FROM my_table ORDER BY age LIMIT 2 ) ORDER BY prenom;
+------+--------+------+
| id   | prenom | age  |
+------+--------+------+
|    4 | ciryl  |   24 |
|    5 | jean   |   30 |
|    2 | louis  |   32 |
|    1 | paul   |   45 |
|    3 | rene   |   68 |
+------+--------+------+
5 rows in set (0.01 sec)

Néanmoins, en partant de votre bonne idée basée sur deux tris successifs, on obtient quelquechose de très correct

Code :

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

1
2
3
4
5
6
7
8
9
 
mysql> (SELECT * FROM my_table ORDER BY age DESC ) ORDER BY prenom LIMIT 2;
+------+--------+------+
| id   | prenom | age  |
+------+--------+------+
|    3 | rene   |   68 |
|    1 | paul   |   45 |
+------+--------+------+
2 rows in set (0.00 sec)

**Sivrît** · 31/05/2006, 19h15

Justement, j'ai essayé avant de poster et sur un MySQL 5.0.21 ça marche.

Par contre avec un 4.1.7 le deuxième ORDER BY semble faire sauter le LIMIT, et le reporter à la fin donne un résultat qui me semble douteux. Mettre deux LIMIT ne semble pas marcher non plus (mais j'ai testé avec la première table venue donc à confirmer).

**Alexandre T** · 31/05/2006, 20h22

Etonnant, je suis en 5.0.18-nt et j'ai obtenu l'étrange résultat que je t'ai communiqué ! Je ne pensais pas qu'entre deux versions si proches on aurait une telle nuance. En tout cas, astuce brillante le double "order by" !

**Sivrît** · 31/05/2006, 20h43

Je ne sais plus où je l'ai vue mais c'est sympatique.

Alors je suis rentré et j'ai essayé sur ma debian. En 5.0.20 ça ne marche pas

. Là j'ai commencé à me poser des questions. Petite mise à jour vers 5.0.21 et ça marche.

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
 
mysql> (SELECT * FROM personne ORDER BY tri DESC LIMIT 2) ORDER BY tri DESC;
+------+------+------+
| nom  | tri  | tri2 |
+------+------+------+
| j    |   10 |    1 |
| i    |    9 |    2 |
+------+------+------+
 
mysql> (SELECT * FROM personne ORDER BY tri DESC LIMIT 2) ORDER BY tri;
+------+------+------+
| nom  | tri  | tri2 |
+------+------+------+
| i    |    9 |    2 |
| j    |   10 |    1 |
+------+------+------+

Bref c'est chouette mais limite expérimental

Je pense qu'en attendant, sur MySQL 5, une requète imbriquée reste la solution la plus performante et la plus lisible.

**warpyou** · 01/06/2006, 00h41

J'ai fait l'upgrade en 5.0.22 cet après-midi, juste avant de partir. Je m'absente quelques jours donc je ne pourrai pas tester avant lundi prochain si la requète avec le subselect est vraiement plus efficace que PHP au "chrono" sur de grandes valeurs, mais j'en doute un peu. Je testerai aussi le comportement du double order by pour voir.
En tout cas merci pour vos propositions

classements multiples en 1 requete uniquement

Requêtes MySQL

Discussions similaires

Partager

Partager