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
> as
> > 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
> and
> > fetch the highest index and increase this with one?
>
> There's no magical shortcut.
>
> I would create an index on (theDate, dateEventNumber).  Then do
>
>     BEGIN
>         SELECT MAX(dateEventNumber) FROM MyTable WHERE theDate = ?1
>         [ in your code see whether you got NULL back, substitute 0 ]
>         INERT INTO MyTable ... ?1 + 1
>     COMMIT
>
> 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:
    UPDATE selectRandom
    SET    lastUsed    = DATE('now', 'localtime')
    ,      lastUsedIdx = (
        SELECT IFNULL(MAX(lastUsedIdx), 0)
        FROM   selectRandom
        WHERE  lastUsed = DATE('now', 'localtime')
    ) + 1
    WHERE  description = :teaToBrew

I would think that is not to convoluted.

-- 
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to