Cas particulier de trie sur un max() group by

**Torg666** · 13/03/2009, 17h11

Create de la table:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
DROP TABLE IF EXISTS `bddtamgo`.`demarr`;
CREATE TABLE  `bddtamgo`.`demarr` (
  `id_demarr` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `dateheure` bigint(20) unsigned NOT NULL,
  `nbsat` int(10) unsigned NOT NULL,
  `idx_numboitier` int(10) unsigned NOT NULL,
  `statut` char(1) NOT NULL,
  PRIMARY KEY (`id_demarr`),
  KEY `FK_demarr_boitier` (`idx_numboitier`),
  CONSTRAINT `FK_demarr_boitier` FOREIGN KEY (`idx_numboitier`) REFERENCES `boitier` (`id_numboitier`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=latin1;

Jeux d'essai:

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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.1.28-rc-community
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
 
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
 
 
--
-- Create schema bddtamgo
--
 
CREATE DATABASE IF NOT EXISTS bddtamgo;
USE bddtamgo;
 
--
-- Definition of table `demarr`
--
 
DROP TABLE IF EXISTS `demarr`;
CREATE TABLE `demarr` (
  `id_demarr` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `dateheure` bigint(20) unsigned NOT NULL,
  `nbsat` int(10) unsigned NOT NULL,
  `idx_numboitier` int(10) unsigned NOT NULL,
  `statut` char(1) NOT NULL,
  PRIMARY KEY (`id_demarr`),
  KEY `FK_demarr_boitier` (`idx_numboitier`),
  CONSTRAINT `FK_demarr_boitier` FOREIGN KEY (`idx_numboitier`) REFERENCES `boitier` (`id_numboitier`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `demarr`
--
 
/*!40000 ALTER TABLE `demarr` DISABLE KEYS */;
INSERT INTO `demarr` (`id_demarr`,`latitude`,`longitude`,`dateheure`,`nbsat`,`idx_numboitier`,`statut`) VALUES 
 (42,43.5481,1.4918,1250000000000,5,21,'D'),
 (43,43.5481,1.4921,1250000000003,5,21,'D'),
 (44,43.5481,1.4921,1250000000004,5,21,'A'),
 (45,43.5499,1.4911,1250000000010,5,21,'A'),
 (46,43,2,1249999999999,8,27,'D'),
 (47,43,1,1250000000004,8,27,'A'),
 (48,43.53329,1.53333,1500000000000,1,28,'D'),
 (49,43.51671,1.56667,1500000000004,1,28,'A'),
 (50,43.51671,1.56667,1500000000005,1,28,'D'),
 (51,43.64651,1.46046,1500000000020,1,28,'A');
/*!40000 ALTER TABLE `demarr` ENABLE KEYS */;
 
 
 
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

J'essai de récupérer les lignes où dateheure est max pour chaque idx_numboitier.

Au final, j'en suis arrivé à la requête suivant:

Code :

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

1
2
3
4
select * from demarr 
where dateheure in (select max(dateheure)
                            from demarr group by idx_numboitier) 
                     group by idx_numboitier;

Hors dans le cas où pour un idx_numboitier j'ai un dateheure max égal à un autre dateheure, la requete renvoie une ligne dont le date heure n'est pas toujours le max pour l'idx_numboitier.

Résultat de la requête:

Code :

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

1
2
3
4
5
 
id_position |latitude |longitude|dateheure         |nbsat| idx_numboitier|statut
44,            43.5481,  1.4921,  1250000000004,  5,        21,                'A'
47,            43,         1,          1250000000004,  8,        27,                'A'
51,            43.64651,1.46046, 1500000000020,  1,        28,                'A'

merci d'avance!!

**SQLpro** · 14/03/2009, 10h49

Votyre requête est effectivement une erreur et une horreur. L'erreur vous l'avez découverte vous même. L'horreur c'est que vous avez mis un GROUP BY à un endroit ou il est parfaitement inutile.

Voici deux exemples de la solutions :

1) avec une sous requête corrélée :

Code :

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

1
2
3
4
5
SELECT * 
FROM   demarr AS T1
WHERE  dateheure = (SELECT max(dateheure)
                    FROM   demarr AS T2
                    WHERE  T1.idx_numboitier = T2.idx_numboitier);

Lisez mon cours sur SQL à ce sujet :
http://sqlpro.developpez.com/cours/s...usrequetes/#L2

2) en utilisant le Row Value Constructor :

Code :

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

1
2
3
4
5
SELECT * 
FROM   demarr
WHERE  (idx_numboitier, dateheure) = (SELECT max(dateheure), idx_numboitier
                                      FROM   demarr
                                      GROUP  BY idx_numboitier);

Lisez mon cours sur SQL à ce sujet :
http://sqlpro.developpez.com/cours/sqlaz/select/#L8

A +

**Arnaud F.** · 14/03/2009, 14h24

Petite erreur pour la seconde requête, c'est plutôt :

Code :

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

1
2
3
4
5
SELECT * 
FROM   demarr
WHERE  (idx_numboitier, dateheure) = (SELECT idx_numboitier, max(dateheure)
                                      FROM   demarr
                                      GROUP  BY idx_numboitier);

++