[thelist] Selecting constants conditionally in SQL

[rudy]

> I want to write an SQL statement that pulls data 
> from a table with a rank column.
> But, I also want it to return constants if the rank 
> is not the lowest or highest ranks in the table.
>
> SELECT
>  name,
>  rank,
>  (4 if this is not the lowest rank in the table,
>       or NULL if it is) AS lowest,
>  (5 if this is not the highest rank in the table,
>       or NULL if it is) AS highest
> FROM foo


hi joshua

you didn't say which database (the sql can be neat or ugly, depending on 
whether you can use subselects), nor did you say which scripting language

and it *sounds* like you have stored the rank numbers in a column, which 
is a completely different problem from assigning ranks numbers as rows are 
returned

if your rank numbers are actually stored in the database, just run this 
query ahead of time --

   select min(rank), max(rank) from foo

and then do your 4/5/null substitution in the scripting language


assuming the rank numbers are not physically stored, you can assign rank 
numbers based on any ORDER BY criteria

rank numbers are really really hard in sql alone, so again, usually you 
want to do this in the scripting language

if it's cold fusion, it would be a piece of cake because there's a query 
variable called CurrentRow that can act as your rank number -- except that 
by itself it's not good enough to indicate ties, you would need to detect 
ties when the current row's value is the same as the previous row's value, 
and zap the rank number

for more information on ties, see my Top Ten SQL article --
   http://evolt.org/index.cfm?menu=8&cid=131
(caveat: there are better ways of doing the Top Ten, but the examples 
illustrating ties are still worthwhile)



holler if you want more info...


rudy