[ALUG] A more subtle (?) sqlite (or actually just SQL) question

6 Nov 2011

      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?

-- 
Chris Green