2018-07-05 5:37 GMT+02:00 Simon Slavin <slav...@bigfraud.org>:
> On 5 Jul 2018, at 3:22am, Cecil Westerhof <cldwester...@gmail.com> wrote:
> > I only want to store a date with a record. But it is possible that more
> > one record will be inserted, so I want to use another field to use as an
> > index. So that the first gets an one, the second a two, etc.
> > Is this possible, or do I just have to check if there is already a date
> > fetch the highest index and increase this with one?
> There's no magical shortcut.
> I would create an index on (theDate, dateEventNumber). Then do
> SELECT MAX(dateEventNumber) FROM MyTable WHERE theDate = ?1
> [ in your code see whether you got NULL back, substitute 0 ]
> INERT INTO MyTable ... ?1 + 1
> You can combine the two commands into one more complicated thing, but I'd
> do that only if I was sure nobody would ever have to figure out why my code
> wasn't working.
I went for the following solution:
SET lastUsed = DATE('now', 'localtime')
, lastUsedIdx = (
SELECT IFNULL(MAX(lastUsedIdx), 0)
WHERE lastUsed = DATE('now', 'localtime')
) + 1
WHERE description = :teaToBrew
I would think that is not to convoluted.
sqlite-users mailing list