On Sun, Jun 28, 2009 at 05:58:39PM -0500, Rick Ratchford scratched on the wall: > Hello. > > I have the following task: > > Suppose that you have a recordset that contains the following: > > DATE > Color1 > Offset1 > > Okay. Now suppose you want to extract from this recordset 15 records only > that are just before the record whose Color1 and Offset1 matches those 15 > records. > > Example: > > Suppose Record number 98 is our REFERENCE record. Color1 = "RED" and Offset1 > = 6. > > I would like to retrieve the most recent 15 records just prior to record 98 > that also have Color1 = "RED" and Offset1 = 6.
Since SQL tables have no inherent ordering, I'm assuming your "Record number 98" is just an arbitrary naming that has no specific relevance to this problem. Along the same lines, the only way to define "just prior" is by the DATE value. > How might this be accomplished? Find all records "WHERE Color1='Red' AND Offset1=6 AND DATE<?". In this case, ? == Date value of the reference record. Sort the results by DATE, descending (ORDER BY). Return only the first 15 records (LIMIT). You might also want to make that "DATE <= ?", grab 16 records, and reject the reference record, depending on how your DATE records are setup. And remember: SQL string literals are in single quotes, so it is 'Red', not "Red". -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
