-
finding MAX inside the same record ?
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
Last edited by Arnijons; 08-31-2007 at 09:42 AM.
-
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
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|