comment faire MAX(col1, col2, colN) ?

**cmako** · 14/01/2009, 13h11

Bonjour,

Je n'arrive pas à trouver comment faire pour trouver une valeur max entre plusieurs colonnes.

Je dois faire une requête de ce genre :

Code :

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

1
2
SELECT ID,MAX(col1, col2, col3, col4, col5, col6, col7, col8), col10, col12
FROM matable

Le problème est que la fonction MAX est une fonction d'agrégation donc il lui faut des données en ligne et pas en colonne
donc j'ai fait un test comme ça :

Code :

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

1
2
3
4
5
6
SELECT MAX(N)
FROM (
	SELECT 4 N UNION 
	SELECT 5 N UNION 
	SELECT 6 N
)t

ça marche, mais lorsque je remplace les chiffres par les nom de colonnes ça ne marche plus.

Que faire? Lorsqu'il y a 2 ou 3 colonnes c'est assez simple de trouver la valeur MAX en faisant:

Code :

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

1
2
3
4
5
6
7
8
CASE WHEN col1>col2 THEN col1 ELSE col2 END
 
-- ou pour 3 colonnes
CASE WHEN col1 > col2 THEN 
	CASE WHEN col1 > col3 THEN col1 ELSE col3 END
ELSE
	CASE WHEN col2 > col3 THEN col2 ELSE col3 END
END

mais lorsqu'il y en a 8 je ne peux plus faire de CASE WHEN ça fera trop de conditions.

Alors y a t'il moyen de s'en sortir sans tables temporaires?

**elsuket** · 14/01/2009, 13h42

Bonjour,

Si vous êtes sous SQL Server 2005, vous pouvez écrire :

Code :

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

1
2
3
4
5
6
7
8
9
10
WITH 
	CTE_Nombres AS
	(
		SELECT a AS Nombres FROM maTable
		UNION ALL SELECT b FROM maTable
		UNION ALL SELECT c FROM maTable
		UNION ALL SELECT d FROM maTable
	)
SELECT MAX(Nombres)
FROM CTE_Nombres;

Ou encore :

Code :

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

1
2
3
4
5
6
7
8
SELECT MAX(Nombres)
FROM
(
	SELECT a AS Nombres FROM maTable
	UNION ALL SELECT b FROM maTable
	UNION ALL SELECT c FROM maTable
	UNION ALL SELECT d FROM maTable
) AS Nombres

Cette dernière requête fonctionnant sur aussi sur SQL Server 2000.

@++

**cmako** · 14/01/2009, 13h55

J'ai impression que les 2 requêtes retournent 1 résultat alors qu'il me faut 1 résultat pour chaque record de matable

par ex:

Code :

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

1
2
3
4
  A B C D     max
  1 1 2 3  --> 3
  1 5 8 0  --> 8
  1 1 7 null --> 7

**SQLpro** · 14/01/2009, 14h32

Le plus simple serait de créer une UDF pour ce faire...

A +

**cmako** · 14/01/2009, 14h48

J'ai trouvé une solution de malade hypermoche, mais qui 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
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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
,CASE WHEN ISNULL(col1, '19000101') > ISNULL(col2, '19000101') 
	AND ISNULL(col1, '19000101') > ISNULL(col3, '19000101')
	AND ISNULL(col1, '19000101') > ISNULL(col4, '19000101')
	AND ISNULL(col1, '19000101') > ISNULL(col5, '19000101')
	AND ISNULL(col1, '19000101') > ISNULL(col6, '19000101')
	AND ISNULL(col1, '19000101') > ISNULL(col7, '19000101')
	AND ISNULL(col1, '19000101') > ISNULL(col8, '19000101')
	AND ISNULL(col1, '19000101') > ISNULL(col9, '19000101')
	AND ISNULL(col1, '19000101') > ISNULL(col10, '19000101')
THEN col1
ELSE
	CASE WHEN ISNULL(col2, '19000101') > ISNULL(col1, '19000101')
		AND ISNULL(col2, '19000101') > ISNULL(col3, '19000101')
		AND ISNULL(col2, '19000101') > ISNULL(col4, '19000101')
		AND ISNULL(col2, '19000101') > ISNULL(col5, '19000101')
		AND ISNULL(col2, '19000101') > ISNULL(col6, '19000101')
		AND ISNULL(col2, '19000101') > ISNULL(col7, '19000101')
		AND ISNULL(col2, '19000101') > ISNULL(col8, '19000101')
		AND ISNULL(col2, '19000101') > ISNULL(col9, '19000101')
		AND ISNULL(col2, '19000101') > ISNULL(col10, '19000101')
	THEN col2
	ELSE
		CASE WHEN ISNULL(col3, '19000101') > ISNULL(col1, '19000101') 
			AND ISNULL(col3, '19000101') > ISNULL(col2, '19000101')
			AND ISNULL(col3, '19000101') > ISNULL(col4, '19000101')
			AND ISNULL(col3, '19000101') > ISNULL(col5, '19000101')
			AND ISNULL(col3, '19000101') > ISNULL(col6, '19000101')
			AND ISNULL(col3, '19000101') > ISNULL(col7, '19000101')
			AND ISNULL(col3, '19000101') > ISNULL(col8, '19000101')
			AND ISNULL(col3, '19000101') > ISNULL(col9, '19000101')
			AND ISNULL(col3, '19000101') > ISNULL(col10, '19000101')
		THEN col3
		ELSE
			CASE WHEN ISNULL(col4, '19000101') > ISNULL(col1, '19000101') 
				AND ISNULL(col4, '19000101') > ISNULL(col2, '19000101')
				AND ISNULL(col4, '19000101') > ISNULL(col3, '19000101')
				AND ISNULL(col4, '19000101') > ISNULL(col5, '19000101')
				AND ISNULL(col4, '19000101') > ISNULL(col6, '19000101')
				AND ISNULL(col4, '19000101') > ISNULL(col7, '19000101')
				AND ISNULL(col4, '19000101') > ISNULL(col8, '19000101')
				AND ISNULL(col4, '19000101') > ISNULL(col9, '19000101')
				AND ISNULL(col4, '19000101') > ISNULL(col10, '19000101')
			THEN col4
			ELSE
				CASE WHEN ISNULL(col5, '19000101') > ISNULL(col1, '19000101') 
					AND ISNULL(col5, '19000101') > ISNULL(col2, '19000101')
					AND ISNULL(col5, '19000101') > ISNULL(col3, '19000101')
					AND ISNULL(col5, '19000101') > ISNULL(col4, '19000101')
					AND ISNULL(col5, '19000101') > ISNULL(col6, '19000101')
					AND ISNULL(col5, '19000101') > ISNULL(col7, '19000101')
					AND ISNULL(col5, '19000101') > ISNULL(col8, '19000101')
					AND ISNULL(col5, '19000101') > ISNULL(col9, '19000101')
					AND ISNULL(col5, '19000101') > ISNULL(col10, '19000101')
				THEN col5
				ELSE
					CASE WHEN ISNULL(col6, '19000101') > ISNULL(col1, '19000101') 
						AND ISNULL(col6, '19000101') > ISNULL(col2, '19000101')
						AND ISNULL(col6, '19000101') > ISNULL(col3, '19000101')
						AND ISNULL(col6, '19000101') > ISNULL(col4, '19000101')
						AND ISNULL(col6, '19000101') > ISNULL(col5, '19000101')
						AND ISNULL(col6, '19000101') > ISNULL(col7, '19000101')
						AND ISNULL(col6, '19000101') > ISNULL(col8, '19000101')
						AND ISNULL(col6, '19000101') > ISNULL(col9, '19000101')
						AND ISNULL(col6, '19000101') > ISNULL(col10, '19000101')
					THEN col6
					ELSE
						CASE WHEN ISNULL(col7, '19000101') > ISNULL(col1, '19000101') 
							AND ISNULL(col7, '19000101') > ISNULL(col2, '19000101')
							AND ISNULL(col7, '19000101') > ISNULL(col3, '19000101')
							AND ISNULL(col7, '19000101') > ISNULL(col4, '19000101')
							AND ISNULL(col7, '19000101') > ISNULL(col5, '19000101')
							AND ISNULL(col7, '19000101') > ISNULL(col6, '19000101')
							AND ISNULL(col7, '19000101') > ISNULL(col8, '19000101')
							AND ISNULL(col7, '19000101') > ISNULL(col9, '19000101')
							AND ISNULL(col7, '19000101') > ISNULL(col10, '19000101')
						THEN col7
						ELSE
							CASE WHEN ISNULL(col8, '19000101') > ISNULL(col1, '19000101') 
								AND ISNULL(col8, '19000101') > ISNULL(col2, '19000101')
								AND ISNULL(col8, '19000101') > ISNULL(col3, '19000101')
								AND ISNULL(col8, '19000101') > ISNULL(col4, '19000101')
								AND ISNULL(col8, '19000101') > ISNULL(col5, '19000101')
								AND ISNULL(col8, '19000101') > ISNULL(col6, '19000101')
								AND ISNULL(col8, '19000101') > ISNULL(col7, '19000101')
								AND ISNULL(col8, '19000101') > ISNULL(col9, '19000101')
								AND ISNULL(col8, '19000101') > ISNULL(col10, '19000101')
							THEN col8
							ELSE
								CASE WHEN ISNULL(col9, '19000101') > ISNULL(col1, '19000101') 
									AND ISNULL(col9, '19000101') > ISNULL(col2, '19000101')
									AND ISNULL(col9, '19000101') > ISNULL(col3, '19000101')
									AND ISNULL(col9, '19000101') > ISNULL(col4, '19000101')
									AND ISNULL(col9, '19000101') > ISNULL(col5, '19000101')
									AND ISNULL(col9, '19000101') > ISNULL(col6, '19000101')
									AND ISNULL(col9, '19000101') > ISNULL(col7, '19000101')
									AND ISNULL(col9, '19000101') > ISNULL(col8, '19000101')
									AND ISNULL(col9, '19000101') > ISNULL(col10, '19000101')
								THEN col9
								ELSE
									col10
								END
							END
						END
					END
				END
			END
		END
	END
END MAXi

**elsuket** · 14/01/2009, 16h07

Je vous propose la fonction suivante :

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
CREATE FUNCTION UFN_GET_MAX
	(@_A INT, @_B INT, @_C INT, @_D INT)
	RETURNS INT
AS
BEGIN
	DECLARE @max INT;
 
	WITH 
		CTE_Nombres (Nombres) AS
		(
			SELECT @_A
			UNION ALL SELECT @_B
			UNION ALL SELECT @_C 
			UNION ALL SELECT @_D 
		)
	SELECT @max = MAX(Nombres)
	FROM CTE_Nombres;
 
	RETURN @max;
END
GO
 
-- Test
DECLARE @Tb TABLE
(
	A INT,
	B INT,
	C INT,
	D INT
)
 
INSERT INTO @Tb VALUES(1, 1, 2, 3)
INSERT INTO @Tb VALUES(1, 5, 8, 0)
INSERT INTO @Tb VALUES(1, 1, 7, NULL)
 
SELECT A, B, C, D, dbo.UFN_GET_MAX(A, B, C, D) AS GREATEST
FROM @Tb