SQLSERVER2005 - GROUP BY

**apnw7931** · 17/02/2011, 14h17

Bonjour, je cherche depuis 1 heure quelque chose de très simple, mais en vain..

Avec une requête (qui contient plusieurs étapes), j'obtiens ces résultats :

Code :

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

1
2
3
4
5
6
7
8
Select ID, PRODUIT
FROM MA_TABLE
 
ID  PRODUIT
--- -------
03    B
02    A
01    C

J'aimerai, dans mon étape suivante, n'avoir qu'une seule ligne avec le plus petit ID, comme tel :

Code :

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

1
2
3
ID  PRODUIT
--- -------
01    C

Mais je n'y arrive pas :

Code :

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

1
2
3
4
5
6
7
8
9
Select MIN(ID), PRODUIT
FROM MA_TABLE
GROUP BY ID
 
ID  PRODUIT
--- -------
03    B
02    A
01    C

**Jinroh77** · 17/02/2011, 14h33

Essayez comme cela :

Code :

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

1
2
3
4
5
6
7
 
WITH Min (Id) AS (
SELECT MIN(Id) FROM MA_TABLE
)
SELECT ID, PRODUIT
FROM MA_TABLE tb
JOIN Min ON Min.Id = tb.Id

**apnw7931** · 17/02/2011, 14h46

Etonnant que cela soit aussi 'compliqué' pour faire si peu..

Donc, merci Jinroh. Ca fonctionne
EDIT : ca ne fonctionne pas.. je reviendrai plus tard avec mon exemple plus complet.. sorry
Maintenant, j'aimerai utiliser ceci pour des reqûetes plus importantes.
Pourrais-tu m'expliquer le fonctionnement du WITH ? Ou me référer un tuto ?
Merci d'avance.

**Jinroh77** · 17/02/2011, 14h55

On peut faire plus simple avec ce genre de chose :

Code :

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

1
2
3
4
 
SELECT ID, PRODUIT
FROM MA_TABLE tb
WHERE tb.Id = (select MIN(ID) from MA_TABLE)

mais je trouve ça moins propre.
Le tout est qu'il faut tout de même parcourir la table pour trouver le MIN de toutes les valeurs.

Pour le WITH, les Common Table Expression en fait tu trouveras des explications et exemples ici.

**apnw7931** · 17/02/2011, 15h07

Je reviens avec mon problème (un rien) plus complexe.

Voici mon input:

Code :

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

1
2
3
4
5
6
7
8
NAME1 NAME2 ID  PRODUIT
----  ----- --- -------
XXX   ZZZ   03    B
XXX   ZZZ   02    A
XXX   ZZZ   01    C
YYY   ZZZ   05    E
YYY   ZZZ   03    B
YYY   ZZZ   02    A

voila le output que j'aimerai avoir :

Code :

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

1
2
3
4
NAME1 NAME2 ID  PRODUIT
----  ----- --- -------
XXX   ZZZ   01    C
YYY   ZZZ   02    A

En appliquant les deux méthodes plus haut, j'obtiens

Code :

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

1
2
3
4
NAME1 NAME2 ID  PRODUIT
----  ----- --- -------
XXX   ZZZ   01    C
YYY   ZZZ   01    C

Comment trouver l'output désiré ?

Merci d'avance

**Waldar** · 17/02/2011, 15h17

Essayez ainsi :

Code :

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

1
2
3
4
5
6
7
8
9
;With SR (NAME1, NAME2, ID, PRODUIT, RN) as
(
select NAME1, NAME2, ID, PRODUIT,
       row_number() over(partition by NAME1 order by ID asc)
  from MaTable
)
select NAME1, NAME2, ID, PRODUIT
  from SR
 where RN = 1

**Jinroh77** · 17/02/2011, 15h20

Plutôt que de donner simplement l'output, essayez de l'expliquer avec vos tables et données réelles.

**apnw7931** · 17/02/2011, 15h37

Voici mon code

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
SELECT										
		  a.E_LOCATION_CODE		AS [LOCATION],
		  a.E_ACCT_NUM			AS [ACCOUNT],
		  MIN(a.TdProdDate)		AS [DATE2],
		  a.E_SERVICE_TYPE		AS [DPD],
 
FROM	dbo.test  AS a 
 
WHERE	a.TdProdDate <= '20101231'
		AND a.TdProdDate >= '20101201'
		AND a.E_LOCATION_CODE = '512111'
		AND a.E_SERVICE_TYPE <= 32 
		AND a.E_SERVICE_TYPE >= 30
		AND a.E_ACCT_NUM = '4563970000306006'
 
GROUP BY	  a.E_LOCATION_CODE,
		  a.E_ACCT_NUM ,
		  a.E_SERVICE_TYPE

et l'ouput qui en résulte :

Code :

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

1
2
3
4
5
6
 
LOCATION     ACCOUNT              DATE          DPD
--------     --------             ------       ----
512111	4563970000306006	2010-12-13 	030
512111	4563970000306006	2010-12-14 	031
512111	4563970000306006	2010-12-15 	032

et l'output que j'aimerai :

Code :

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

1
2
3
4
 
LOCATION     ACCOUNT              DATE          DPD
--------     --------             ------       ----
512111	4563970000306006	2010-12-13 	030

En esperant que ce soit plus explicite.
Pour info, la condition "a.E_ACCT_NUM = '4563970000306006" n'est présente que pour alleger mon exemple. Normalement, je souhaite le faire pour tous les comptes de ma table. (Donc, une ligne pour chaque compte).

JE vous remercie pour votre aide précieuse.

**Waldar** · 17/02/2011, 15h45

Même méthode que plus haut :

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
;WITH SR (E_LOCATION_CODE, E_ACCT_NUM, TdProdDate, E_SERVICE_TYPE, RN) as
(
SELECT E_LOCATION_CODE,
       E_ACCT_NUM,
       TdProdDate,
       E_SERVICE_TYPE,
       row_number() over(partition by E_ACCT_NUM, E_LOCATION_CODE order by TdProdDate asc)
  FROM dbo.test
 WHERE TdProdDate BETWEEN '20101201' AND '20101231'
   AND E_SERVICE_TYPE BETWEEN 30 AND 32
)
SELECT E_LOCATION_CODE AS [LOCATION],
       E_ACCT_NUM      AS [ACCOUNT] ,
       TdProdDate      AS [DATE2]   ,
       E_SERVICE_TYPE  AS [DPD]
  FROM SR
 WHERE RN = 1

**apnw7931** · 17/02/2011, 16h41

Waldar (et autres),

j'ai maintenant utilisé ton code.
Celui ci fonctionne nickel.
Je souhaite poursuivre ma requête comme suit :

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
WITH WITH_1 (E_LOCATION_CODE, E_ACCT_NUM, TdProdDate, E_SERVICE_TYPE, RN) AS
(
	SELECT E_LOCATION_CODE,
		   E_ACCT_NUM,
		   TdProdDate,
		   E_SERVICE_TYPE,
		   row_number() over(partition BY E_ACCT_NUM, E_LOCATION_CODE ORDER BY TdProdDate ASC)
 
	FROM dbo.TESTas a
 
	WHERE a.TdProdDate BETWEEN '20101201' AND '20101231'
		  AND a.E_LOCATION_CODE = '512111'
		  AND a.E_SERVICE_TYPE <= 32 
		  AND a.E_SERVICE_TYPE >= 30
)
--------------------------------------------------------------------------------------------------------
 
SELECT 
			a.E_LOCATION_CODE,
			a.E_ACCT_NUM,
			a.TdProdDate,
			a.E_SERVICE_TYPE,
			b.TdProdDate,
			b.E_SERVICE_TYPE
 
FROM	WITH_1 as a
			LEFT JOIN dbo.TEST2 as b
				ON a.E_ACCT_NUM = b.E_ACCT_NUM
				AND b.E_SERVICE_TYPE >= 20
				AND b.E_SERVICE_TYPE <= 22
				AND b.TdProdDate <= a.TdProdDate
				AND b.TdProdDate >= a.TdProdDate - 11
 
WHERE		RN = 1

J'ai donc joins une autre table, et rajouter 2 colonnes.
Je souhaite refaire exactement la même chose que mon premier with, c'est-a-dire ne garder toujours qu'une seule ligne par compte, mais avec le minimum de la premiere date (ok avec le premier with) ET avec le minimum de la deuxieme DATE (??).

Je n'ai pas encore réussi.
Si vous pouviez m'éclairer pour imbriquer un deuxième WITH dans un premier.

Merci d'avance

Voici ce que j'ai rajouté pour l'instant :

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
WITH WITH_2 (a.E_LOCATION_CODE, a.E_ACCT_NUM, a.TdProdDate, a.E_SERVICE_TYPE, b.TdProdDate, b.E_SERVICE_TYPE, RN2) AS
(
SELECT 
			a.E_LOCATION_CODE
			, a.E_ACCT_NUM
			, a.TdProdDate
			, a.E_SERVICE_TYPE
			, b.TdProdDate
			, b.E_SERVICE_TYPE
			, row_number() over(partition BY a.E_ACCT_NUM, a.E_LOCATION_CODE ORDER BY b.TdProdDate ASC)
 
FROM	WITH_1 as a
			LEFT JOIN dbo.TEST2as b
				ON a.E_ACCT_NUM = b.E_ACCT_NUM
				AND b.E_SERVICE_TYPE >= 20
				AND b.E_SERVICE_TYPE <= 22
				AND b.TdProdDate <= a.TdProdDate
				AND b.TdProdDate >= a.TdProdDate - 11
 
WHERE		RN = 1
 
)
 
SELECT 		a.E_LOCATION_CODE
			, a.E_ACCT_NUM
			, a.TdProdDate
			, a.E_SERVICE_TYPE
			, b.TdProdDate
			, b.E_SERVICE_TYPE
 
FROM WITH_2
 
WHERE RN2 = 1

Mais je me retrouve avec ce message d'erreur :

Code :

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

1
2
Msg 319, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

**Waldar** · 18/02/2011, 01h43

Pour mettre un deuxième WITH, il faut juste utiliser une virgule :

Code :

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

1
2
3
4
5
;With Req1 as
(...)
   ,  Req2 as
(...)
select...

**apnw7931** · 18/02/2011, 09h27

Ce la fonctionne pas.
J'ai un message d'erreur au niveau de la virgule qui sépare les 2 Requests

Code :

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

1
2
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near '.'.

Une idée ? Serait-ce lié au fait que ma deuxième requete dans mon WITH dépend du premier ?

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
WITH 
	SR (E_LOCATION_CODE, E_ACCT_NUM, TdProdDate, E_SERVICE_TYPE, RN) AS 
	(
		SELECT E_LOCATION_CODE,
			   E_ACCT_NUM,
			   TdProdDate,
			   E_SERVICE_TYPE,
			   row_number() over(partition BY E_ACCT_NUM, E_LOCATION_CODE ORDER BY TdProdDate ASC)
 
		FROM dbo.TEST as a
 
		WHERE a.TdProdDate BETWEEN '20101201' AND '20101231'
			  AND a.E_LOCATION_CODE = '512111'
			  AND a.E_SERVICE_TYPE <= 32 
			  AND a.E_SERVICE_TYPE >= 30
 
	)
 
	, SR2  (a.E_LOCATION_CODE, a.E_ACCT_NUM, a.TdProdDate, a.E_SERVICE_TYPE, b.TdProdDate, b.E_SERVICE_TYPE, RN2) AS
 
	(
		SELECT 
					a.E_LOCATION_CODE
					, a.E_ACCT_NUM
					, a.TdProdDate
					, a.E_SERVICE_TYPE
					, b.TdProdDate
					, b.E_SERVICE_TYPE
					, row_number() over(partition BY a.E_ACCT_NUM, a.E_LOCATION_CODE ORDER BY b.TdProdDate ASC)
 
		FROM	SR as a
					LEFT JOIN dbo.TEST as b
						ON a.E_ACCT_NUM = b.E_ACCT_NUM
						AND b.E_SERVICE_TYPE >= 20
						AND b.E_SERVICE_TYPE <= 22
						AND b.TdProdDate <= a.TdProdDate
						AND b.TdProdDate >= a.TdProdDate - 11		
	)
 
--------------------------------------------------------------------------------------------------------
SELECT 
			a.E_LOCATION_CODE
			, a.E_ACCT_NUM
			, a.TdProdDate
			, a.E_SERVICE_TYPE
			, b.TdProdDate
			, b.E_SERVICE_TYPE
 
FROM	SR2
 
WHERE RN2 = 1

**Waldar** · 18/02/2011, 10h57

Vous avez mis un alias de table dans le nom des colonnes retournée par la seconde CTE :

Code :

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

, SR2  (a.E_LOCATION_CODE, a.E_ACCT_NUM, a.TdProdDate, a.E_SERVICE_TYPE, b.TdProdDate, b.E_SERVICE_TYPE, RN2) AS

=>

Code :

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

, SR2  (E_LOCATION_CODE, E_ACCT_NUM, TdProdDate_1, E_SERVICE_TYPE_1, TdProdDate_2, E_SERVICE_TYPE_2, RN2) AS

SQLSERVER2005 - GROUP BY

MS SQL Server

Discussions similaires

Partager

Partager