Requête imbriquée avec deux "rangements par ordre de" [Résolu]

[reivinternet]

Bonjour,

Depuis plusieurs jours, j'essaie de trouver une solution simple, c'est -à-dire en une seule requête, à un problème de order by ou group by ou autres. Prenons un exemple pour expliquer ce que je recherche. Au départ, nous avons une table avec 2 champs : date et user. Comment ranger par ordre décroissant de date, puis pour chaque par ordre alphabétique des user.

Table de départ :
2010-01-01 | Michel
2010-01-04 | Michel
2010-01-04 | Michel
2010-01-02 | Michel
2010-01-04 | Vincent
2010-01-04 | Vincent
2010-01-02 | Suzanne
2010-01-03 | Suzanne
2010-01-03 | Bertrand
2010-01-04 | Bertrand

Résultat attendu :
2010-01-04 | Bertrand
2010-01-03 | Bertrand
2010-01-04 | Michel
2010-01-04 | Michel
2010-01-02 | Michel
2010-01-01 | Michel
2010-01-04 | Vincent
2010-01-04 | Vincent
2010-01-03 | Suzanne
2010-01-02 | Suzanne

En gros rangement par ordre décroissant de date, puis pour chaque date quand on rencontre un user on affiche tous les enregistrements de ce user par ordre décroissant de date...
Facile, hein !!!

En tout cas, merci pour votre aide.

[vmolines]

http://sqlpro.developpez.com/cours/sqlaz/select/#L2

[reivinternet]

Merci vmolines pour votre réponse, mais cela ne correspond pas à ce que je recherche.
En effet en reprenant la table de départ en faisant un double order by, j'obtiendrai :
2010-01-04 | Bertrand
2010-01-04 | Michel
2010-01-04 | Michel
2010-01-04 | Vincent
2010-01-04 | Vincent
2010-01-03 | Bertrand
2010-01-03 | Suzanne
2010-01-02 | Michel
2010-01-02 | Suzanne
2010-01-01 | Michel

Alors que le résultat attendu est :
2010-01-04 | Bertrand
2010-01-03 | Bertrand
2010-01-04 | Michel
2010-01-04 | Michel
2010-01-02 | Michel
2010-01-01 | Michel
2010-01-04 | Vincent
2010-01-04 | Vincent
2010-01-03 | Suzanne
2010-01-02 | Suzanne

A nouveau merci quand même...

[vmolines]

Code :

... ORDER BY Nom, Date DESC

Non ?

Et attention DATE est un mot clé SQL donc à éviter pour nommer des objets de la base de données.

[reivinternet]

Non, toujours pas.
Je ne pense pas qu'un simple order by soit la réponse. Cela doit certainement passer par une requête imbriquée.

Pour info, sous PostGreSQL, date n'est pas un mot réservé et heureusement...

A nouveau merci de ton aide.

[CinePhil]

Regarde du côté de ROW_NUMBER ou autres fonctions de fenêtrage.
L'idée qui me vient à l'esprit étant de classer les dates distinctes et de leur affecter un ROWNUM dans une sous requête puis de faire une jointure sur la date et d'utiliser ce ROWNUM pour l'ordre.

Pas le temps de creuser davantage.

Mais j'ai l'impression que tu cherches à afficher les données d'une certaine manière, ce qui est plutôt le boulot du programme utilisateur que du SGBD. Cela s'appelle de la cosmétique.

[reivinternet]

CinePhil, je vais regarder cela de plus près.
Si je trouve quelque chose, je vous tiens au courant.

Pour votre question, l'intérêt de cette requête est un gain de temps. Cela évite de faire 2 requêtes ou de faire une requête globale puis un traitement avec un langage (PHP ou autres).

En tout cas, merci pour le conseil.

[CinePhil]

Envoyé par reivinternet

Cela évite de faire 2 requêtes ou de faire une requête globale puis un traitement avec un langage (PHP ou autres).

Il n'est pas toujours évident qu'une requête + un traitement dans le programme soit plus long qu'une requête complexe. Lire l'article de SQLPro sur la cosmétique, je crois qu'il en parle.

Et sur de petits volumes de données, la différence n'est pas sensible.

Le rôle du SGBD, c'est de cracher les données selon les critères de recherche qu'on lui demande. La présentation des données, c'est le boulot du programme.

À la limite, ORDER BY pourrait ne pas exister en SQL !

[estofilo]

Il faut découper le problème en 2: d'abord trouver l'ordre d'affichage de chaque nom, et ensuite afficher toute la table avec cet ordre là en primaire, et la date en ordre secondaire
Pour exprimer la chose en sql, disons que la table s'appelle t et les colonnes u pour user et d pour date.

L'ordre d'affichage de chaque nom est donné par:

Code :

SELECT u, rank() over (ORDER BY max(d) DESC, u ASC) AS o FROM t GROUP BY u

Ensuite il faut joindre avec la table et ordonner avec le rang calculé au-dessus, ce qui donne:

Code :

1
2
3
4
 
SELECT l.u,d FROM t,
(SELECT u, max(d), rank() over (ORDER BY max(d) DESC, u ASC) AS o FROM t GROUP BY u) AS l
WHERE l.u=t.u ORDER BY o, l.u, d DESC;

A noter qu'il faut postgres 8.4 minimum pour avoir la fonctionnalité de fenêtrage.

[CinePhil]

LE spécialiste es Postgresql estofilo a su traduire en SQL la vague idée que j'avais eue, sauf qu'il fallait donner un rank à l'utilisateur et pas à la date.

Un petit bémol sur ta requête estofilo : les jointures utilisent depuis 1992 l'opérateur JOIN !

Code :

1
2
3
4
5
6
7
8
9
SELECT l.u,d FROM t
INNER JOIN
(
    SELECT u, max(d), 
        rank() over (ORDER BY max(d) DESC, u ASC) AS o 
    FROM t 
    GROUP BY u
) AS l ON l.u=t.u 
ORDER BY o, l.u, d DESC;

[reivinternet]

Je m'incline devant une telle requête
Il y a un certain nombre d'éléments que je ne comprends pas, le "rank over" et le "as l on l.u=t.u", mais cela est secondaire, car le problème principal est que PostgreSQL renvoit une erreur de syntaxe :
ERROR: syntax error at or near "over"
inner join (select u, max(d), rank() over (orde...
Pour info, je suis sur postgresql 8.3.8

[CinePhil]

Je ne me prononcerai pas sur la partie rank, cause semble t-il de l'erreur car je ne l'ai jamais utilisée.

Envoyé par reivinternet

Il y a un certain nombre d'éléments que je ne comprends pas, (...) et le "as l on l.u=t.u",

AS l : on affecte un alias à la sous-requête pour la considérer comme une pseudo table sur laquelle on peut faire une jointure.
ON l.u = t.u : C'est la condition de la jointure.

Une jointure s'écrit :

Code :

1
2
FROM une_table
[INNER] [LEFT] [RIGHT] [OUTER] JOIN une_autre_table ON condition_de_jointure

[reivinternet]

Ok.

Merci pour votre aide.
Je vais à nouveau regarder cela de plus près...

[estofilo]

rank() OVER() est une fonction de fenêtrage et n'est supporté par postgresql qu'à partir de la 8.4

En l'absence de fenêtrage, il y a bien une solution laide qui consiste à utiliser une séquence pour générer l'ordonnancement:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE TEMPORARY SEQUENCE s;
 
SELECT l.u,d FROM t
INNER JOIN
(
  SELECT NEXTVAL('s') AS o, u, md FROM (
    SELECT u, max(d) AS md
    FROM t 
    GROUP BY u
    ORDER BY 2 DESC, 1 ASC) AS l2
) AS l ON l.u=t.u 
ORDER BY o, l.u, d DESC;

la séquence n'ayant pas besoin d'être réinitialisée à chaque appel de la requête.

[aieeeuuuuu]

salut

sinon, sans sequence :

Code sql :

1
2
3
4
5
6
7
8
9
 
SELECT dte, test2.nom
FROM test
INNER JOIN (
	SELECT nom, max(dte)
	FROM test
	GROUP BY nom
) AS t2(nom, maxdte) ON test.nom = t2.nom
ORDER BY t2.maxdte DESC, test.nom, test.dte DESC

[reivinternet]

aieeeuuuuu, il doit y avoir une erreur sur les jointures car cela ne passe pas.
Merci en tout cas.

Je continue de regarder et vous tiens au courant.

[CinePhil]

cela ne passe pas.

En quoi cela ne passe pas ?
Message d'erreur ?
Pas le bon résultat ?

Essaie comme ça :

Code :

1
2
3
4
5
6
7
8
SELECT dte, test2.nom
FROM test
INNER JOIN (
    SELECT nom, max(dte) AS maxdte
    FROM test
    GROUP BY nom
) AS t2 ON test.nom = t2.nom
ORDER BY t2.maxdte DESC, test.nom, test.dte DESC

[reivinternet]

Avec l'alias, cela passe très bien.

Merci sincèrement à tous de votre aide.