finding MAX in same record

[Arnijons]

Hi
What is the best way to find max inside the same record.
I do have 24 numeric columns (one for each hour in the day) in each record, and would like to do that without using houndreds of IF statements.

bye
Arni

[nosepicker]

For something like this, I would typically pivot the columns into a more vertical table. For example, if your original table is like this:

CREATE TABLE YourTable (
primarykey int not null,
hour1 int null,
hour2 int null,
hour3 int null,
hour4 int null,
hour5 int null)


Then you can pivot it into this:

CREATE TABLE #tmp(
primarykey int not null,
hours int null)

INSERT INTO #tmp
SELECT primarykey, hour1 FROM YourTable
UNION ALL
SELECT primarykey, hour2 FROM YourTable
UNION ALL
SELECT primarykey, hour3 FROM YourTable
UNION ALL
SELECT primarykey, hour4 FROM YourTable
UNION ALL
SELECT primarykey, hour5 FROM YourTable

And then find the max:

SELECT primarykey, MAX(hours) FROM #tmp GROUP BY primarykey

[skhanal]

If it is Oracle then there is a function called GREATEST which returns MAX value when passed a list of values.

SELECT GREATEST(col1, col2, .... col100)
FROM table1

If it is SQL Server you will need to write a function which takes a comma separated list and you can calculate max in the function.