Embarrassingly, I already solved this for another problem:

SELECT FIRST 1
*
FROM
MY_TABLE t1
WHERE
t1.UNIQUE_CONSTRAINTS = ? AND
t1.V1 < :GIVEN_NUMBER
ORDER BY
t1.V1 DESC

However, this doesn't work for V1 less than 10, nor do the other
suggestions. Which turns out is OK, as my example gave more detail than
what the actual business need was.

Strangely, this query doesn't use the unique constraint in its plan:

PLAN (T1 ORDER I_V1_INDEX)

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
Sent: Monday, February 05, 2007 12:54 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Query help needed

Given the number 25, I'd like to get the value 'B' back from the
following (simplified) table:

V1 V2
-- --
10 A
20 B
30 C

Numbers 30 and above should return 'C', and numbers below 20 should
return 'A'.
This should be simple, but I just can't think today.

Thanks, Rick DeBay

Disclaimer: This message (including attachments) is confidential and may
be privileged. If you have received it by mistake please notify the
sender by return e-mail and delete this message from your system. Any
unauthorized use or dissemination of this message in whole or in part is
strictly prohibited. Please note that e-mails are susceptible to change.
RxStrategies, Inc. shall not be liable for the improper or incomplete
transmission of the information contained in this communication or for
any delay in its receipt or damage to your system. RxStrategies, Inc.
does not guarantee that the integrity of this communication has been
maintained nor that this communication is free from viruses,
interceptions or interference.




++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the
main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links