comment faire MAX(col1, col2, colN) ?

Version imprimable

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:

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:

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:

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?

Bonjour,

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

Code:

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:

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.

@++ ;)

14/01/2009, 13h55
cmako
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:

1 2 3 4 A B C D max 1 1 2 3 --> 3 1 5 8 0 --> 8 1 1 7 null --> 7
14/01/2009, 14h32
SQLpro

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

A +

J'ai trouvé une solution de malade hypermoche, mais qui marche :

Code:

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

Je vous propose la fonction suivante :

Code:

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

Citation:

A B C D GREATEST
----------------------------------------
1 1 2 3 3
1 5 8 0 8
1 1 7 NULL 7

@++ ;)

14/01/2009, 17h51
cmako

Citation:

Envoyé par elsuket

Je vous propose la fonction suivante :

@++ ;)

J'aime bien ta fonction, merci. :king: