Re: Trying to fetch records only if preceded by at least another one

On Fri, Oct 25, 2019 at 2:20 PM Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
wrote:

> Thank you Michael -
>
> On Fri, Oct 25, 2019 at 7:28 PM Michael Lewis <mlewis(at)entrata(dot)com> wrote:
>
>> But it prints too many records: all moves cross-multiplied with each
>>> other.
>>>
>>> As if I have forgotten to add 1 more condition to the JOIN LATERAL
>>>
>>
>> LIMIT 1 inside your lateral should resolve that. Personally, I'd move
>> that condition to EXISTS condition inside WHERE clause instead. Just a
>> style thing as far as I know.
>>
>
> How would you do that with EXISTS? I tried, but my query failed to compile
>

Ooops. I didn't notice you had selected the previous move ID. In that case,
at the end of the lateral you want ORDER BY mid DESC LIMIT 1, or perhaps
order by played field and you should be good to go. Particularly if you
have an index on (gid, mid) then it should be very fast. If you want to
quickly find these "interesting moves" and they are very rare, a partial
index would be quite helpful. Highly stylized, but when it is indexing 1-5%
of a huge table perhaps, it can be a very handy tool.

CREATE INDEX idx_interesting_moves ON words_moves USING btree( played )
WHERE action='play' AND length(letters) = 7 and length(hand)=7;