I have a database which, among other things has a date column and also has some text columns. Is there any way to ask for:-

The value of a text column for the last date less than the current date

E.g. I have data like the following:-

Date Text --------- --------- 2011-11-4 Cabbages 2011-11-5 Fireworks 2011-11-7 Nutcase

and I want the result 'Fireworks' given the date 2011-11-6.

SELECT Text from db WHERE Date < '2011-11-6';

will give all previous rows, not just the row I want. I sort of need to to say:-

SELECT Text from db WHERE Date 'is the biggest one less than' '2011-11-6';

I guess I can do:-

SELECT Text from db WHERE Date < '2011-11-6' ORDER BY Date DESCENDING;

and use the first row returned but this somehow seems inelegant.

Does anyone have any better ideas?