Simple Query Help

[alwayshouston]

Hi All
I am kind of stuck on this simple query that I need to create. I have written a function for this, but I get an error. I am wandering if I can get the results without writing a function. Below is the data and I need to find Max_Score for each row via SQL

Company_ID Score1 Score2 Score3
1000000000 5 10 15
1000000001 1 -5 0
1000000002 355 254 47

My output should be :
Company_ID Score1 Score2 Score3 Max_Score
1000000000 5 10 15 15
1000000001 1 -5 0 1
1000000002 355 254 47 355


Thanks in Advance!

 

[psemianonymous]

You might as well do this with a function. There is a Min() and a Max() function that you can use inside queries, but they compare values between rows, not between columns. So yeah, use your custom function.

Show the code (and show the SQL for the query you're building) and we'll see what's going wrong. Oh, and please mention the error message.

 

[scriverb]

Select A.[CompanyID], A.Score1, A.Score2, A.Score3, Switch((A.Score1 > A.Score2 and A.Score1 > A.Score3), A.Score1, (A.Score2 > A.Score1 and A.Score2 > A.Score3), A.Score2, (A.Score3 > A.Score1 and A.Score3 > A.Score2), A.Score3, True, A.Score1) as Max_Score
FROM [i]YourTableName[/i] as A 
ORDER BY A.[CompanyID];

The Switch function statement analyzes the three scores and returns the largest. If all are the same then the True expression returns Score1.

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[OhioSteve]

There are many ways to do what you describe. This would be my approach:

SELECT
table1.Company_ID,
table1.Score1,
table1.Score2,
table1.Score3,
IIf([score1]>[score2],[score1],[score2]) AS x,
IIf([x]>[score3],[x],[score3]) AS highest
FROM table1;

This will display an extra field. However, I assume that the users will not see the actual query. They will see a form based on the query. Just don't display "x" on the form. By the way, it won't work if you uncheck the "show" box for "x". The machine must first calculate "x". Then it can calculate the field you really want (highest).

I test this and it worked.

 

[alwayshouston]

Thanks All!