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?