Well, there are aggregate functions, so if had three rows rather than one row with three values, I'd expect

SELECT CandidateNo, MAX(rating) - MIN(rating) AS RatingDiff
FROM Candidate
GROUP BY CandidateNo

to produce the result you desire. If you want to keep these values in separate fields, I'd recommend writing a simple stored procedure. But I'd expect

SELECT case when Rating1 > coalesce(Rating2, 0)
and Rating1 > coalesce(Rating3, 0)
and coalesce(Rating2, 0) > coalesce(Rating3, 0) then Rating1 - Rating3
when Rating1 > coalesce(Rating2, 0)
and Rating1 > coalesce(Rating3, 0)
and coalesce(Rating2, 0) <= coalesce(Rating3, 0) then Rating1 - Rating2
when Rating2 > coalesce(Rating1, 0)
and Rating2 > coalesce(Rating3, 0)
and coalesce(Rating1, 0) > coalesce(Rating3, 0) then Rating2 - Rating1
when Rating2 > coalesce(Rating1, 0)
and Rating2 > coalesce(Rating3, 0)
and coalesce(Rating2, 0) <= coalesce(Rating3, 0) then Rating2 - Rating3
when Rating3 > coalesce(Rating2, 0)
and Rating3 > coalesce(Rating1, 0)
and coalesce(Rating2, 0) > coalesce(Rating1, 0) then Rating3 - Rating1
when Rating3 > coalesce(Rating2, 0)
and Rating3 > coalesce(Rating1, 0)
and coalesce(Rating2, 0) <= coalesce(Rating1, 0) then Rating3 - Rating2
end AS RatingDiff
FROM Candidate

to also give you what you want (provided the rating always is a positive value and that at least two of the three ratings always exists).

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of ai_no_kareshi
Sent: 28. januar 2008 14:07
To: firebird-support@yahoogroups.com
Subject: [firebird-support] The greater/lesser of a number of values

Does Firebird provide functions for determining the greater/lesser of
two or more values?

For instance, I have a table that stores three ratings per candidate.
What I want is to calculate, for each record, the difference between
the largest of the three ratings and the smallest of the three ratings.

Suppose there were functions like these and they were called MAX_VAL
and MIN_VAL, my query would look something like this:

SELECT MAX_VAL(Rating1, Rating2, Rating3) - MIN_VAL(Rating1, Rating2,
Rating3) AS RatingDiff
FROM Candidate

If there aren't any functions like these, I would appreciate it if
someone could suggest a different approach to solving this problem.