how to get maximum value of multiple columns with where clause?

[omar999]

i can a max value of a single column with where clause like this

select MAX(Price_Band_1) AS HighestPrice from UK_Specials where ID IN (1,3,5,7,9,10,13,15,17,19,21,23,25,27,29,31,33,35,37)
 

this works fine but I need to obtain max value of multiple columns. would it be something like this???

select MAX(Price_Band_1),  MAX(Price_Band_2), MAX(Price_Band_3), MAX(Price_Band_4) AS HighestPrice from UK_Specials where ID IN (1,3,5,7,9,10,13,15,17,19,21,23,25,27,29,31,33,35,37)
 

many thanks in advance
Omar.

[ck9663]

Well did you try the it?

Happy Coding!!!

~~ CK

[omar999]

hey ck9663

sorry what do you mean?

[ck9663]

I mean did you try this code?

   select MAX(Price_Band_1),  MAX(Price_Band_2), MAX(Price_Band_3), MAX(Price_Band_4) AS HighestPrice from UK_Specials where ID IN (1,3,5,7,9,10,13,15,17,19,21,23,25,27,29,31,33,35,37)
 
 

[omar999]

i tried the code and it returns an incorrect value without any errors on the page.
I believe there is some sort of mistake in my select statement logic but i cant see where

[nbiswas]

Let us start with an example.

Say I have a table(e.g. UK_Specials) something like the following

Id     Price_Band_1      Price_Band_2       Price_Band_3        Price_Band_4
---   ----------------------     ---------------------       --------------------       ----------------------
 
1        10                        20                           30                     40
 
2        35                       12                            29                     33
 
3        11                       298                         12                      78
 
4        56                        11                         34                      55

Since you are interested to find the maximum value of the four columns for some specified Id's , so I can say that for the above described scenario, the value will be 298 for the Id's 1,2,3,4 among all the four column.

If this understanding of mine is correct, then use any of the following

Query 1:

Select HighestPrice = MAX(HighestPrice) from (
Select Price_Band_1 As HighestPrice from UK_Specials where Id in (1,2,3,4) UNION ALL
Select Price_Band_2 from UK_Specials where Id in (1,2,3,4) 
UNION ALL
Select Price_Band_3 from UK_Specials where Id in (1,2,3,4) 
UNION ALL
Select Price_Band_4 from UK_Specials where Id in (1,2,3,4) )x

Query 2:

Select HighestPrice = MAX(HighestPrice) from (
Select MAX(Price_Band_1) As HighestPrice from UK_Specials where Id in (1,2,3,4) UNION ALL
Select MAX(Price_Band_2) from UK_Specials where Id in (1,2,3,4) 
UNION ALL
Select MAX(Price_Band_3) from UK_Specials where Id in (1,2,3,4) 
UNION ALL
Select MAX(Price_Band_4) from UK_Specials where Id in (1,2,3,4) )x

Output:

HighestPrice
298

Note: ~
If you are using Sql Server 2005+, then instead of doing the above, first you UNPIVOT
and then apply the MAX function to achieve the same.

Hope this helps
Let me know in case of any concern.

[omar999]

this has worked beautifully! thank you ever so much and have a wonder ful day

kind regards
Omar : )

[omar999]

hi - im now using this method to return the minimum value from selected columns. however for some non obvious reason it only works when a 3digit long number is entered? below is my sql statement;

Select CheapestPrice = MIN(CheapestPrice) from (Select MIN(Price_Band_1) As CheapestPrice from mytablename 
where Id in (1,3) UNION ALL Select MIN(Price_Band_2) from mytablename 
where Id in (1,3) UNION ALL Select MIN(Price_Band_3) from mytablename 
where Id in (1,3) UNION ALL Select MIN(Price_Band_4) from mytablename where Id in (1,3) )x

my table with price_band columns, values in bold;

ID | Flight_Route | Price_Band_1 | Month_Band_1 | Date_Band_1 | Price_Band_2 | Month_Band_2 | Date_Band_2 | Price_Band_3 | Month_Band_3 | Date_Band_4 | Price_Band_4 | Month_Band_4 | Date_Band_4
1    TEST1    99    MAY    12, 19, 31    199    JUNE    12, 19, 31    199    JULY    12, 19, 31    199    AUG    12, 19, 31
3    TEST3    199    MAY    12, 19, 31    199    JUNE    12, 19, 31    199    JULY    12, 19, 31    199    AUG    12, 19, 31
 

the above code returns 199 when it really should return 99... any ideas as to why?

please advise
Omar.

[t3oGazz]

The same problem happens if you use MAX function.
In that case, if you try to use numbers like 123, 234 and 5 the result will be 5 that means that the function look only at the first digit of the number.

I've solved this issue by force the values as INT.
So in your case it could be something like the following:

DECLARE @value1 int, @value3 int, @value2 int

SELECT @value1 = Select Price_Band_1 from UK_Specials where Id in (1,2,3,4)
SELECT @value2 = Select Price_Band_2 from UK_Specials where Id in (1,2,3,4)
SELECT @value3 = Select Price_Band_3 from UK_Specials where Id in (1,2,3,4)

Select HighestPrice = MAX(HighestPrice) from (
SELECT HighestPrice = @value1
UNION ALL
SELECT HighestPrice = @value2
UNION ALL
SELECT HighestPrice = @value3
)x

Sorry for my poor english, hope this helps!
Matteo.