Unfortunately, Eric's system will never learn of the glory of the King in
Yellow.

On Mon, Jul 24, 2017 at 7:26 AM, Timothy Cornwell <[log in to unmask]> wrote:

> The righteous crimson king solved all the problems Your evil red king over
> taxes us.
>
>
>
> -----Original Message-----
> From: Code for Libraries [mailto:[log in to unmask]] On Behalf Of
> Eric Lease Morgan
> Sent: Saturday, July 22, 2017 10:36 AM
> To: [log in to unmask]
> Subject: Re: [CODE4LIB] good kings & bad kings: an sql query [resolved]
>
> On Jul 22, 2017, at 8:47 AM, Andreas Orphanides <[log in to unmask]> wrote:
>
> >> How might I write an SQL query to find all the descriptions of good
> >> kings and bad kings in my database?
> >>
> >> I have a table that looks something like this:
> >>
> >>  key  word       part_of_speech  position
> >>  ---  ---------  --------------  --------
> >>  w1   The        article         1
> >>  w2   righteous  adjective       2
> >>  w3   king       noun            3
> >>  w4   solved     verb            4
> >>  w5   all        adjective       5
> >>  w6   the        article         6
> >>  w7   problems   noun            7
> >>  w8   Your       noun            8
> >>  w9   evil       adjective       9
> >>  w10  king       noun            10
> >>  w11  over       adverb          11
> >>  w12  taxes      verb            12
> >>  w13  us         pronoun         13
> >
> > In a perfect world, your parser would include the referents for the
> > adjectives, which would solve the problem lickety split, but I assume
> > that's not an option here. Are you ok with only discovering
> > adjective-noun pairs?...
> >
> > Add a sequentially numbered autogen column that's pure integer.
> > (Unless you have string manipulation -- in which case you could just
> work on the "key"
> > column).
> >
> > Then something like:
> >
> > SELECT * FROM table WHERE autogen IN (SELECT (autogen-1) FROM table
> > WHERE word = "king") AND part_of_speech=“adjective"
>
>
> Resolved, and the secret sauce was the SQL qualifier IN.
>
> To find all positions of the word “king”, I can do this:
>
>   SELECT position FROM words WHERE word=“king”;
>
> To find all the positions prior to the word “king”, I can do this:
>
>   SELECT position-1 FROM words WHERE word=“king”;
>
> I can then use the IN qualifier to use the results of the previous query
> as input for a subsequent query. Thus I can find all the words prior to the
> word “king”;
>
>   SELECT word
>   FROM words
>   WHERE position IN (SELECT position-1 FROM words WHERE word=“king”);
>
> I can then further limit my query to only list adjectives:
>
>   SELECT word
>   FROM words
>   WHERE part_of_speach=“adjective”
>   AND position IN (SELECT position-1 FROM words WHERE word=“king”);
>
> I can then count and group the results as an ordered list:
>
>   SELECT COUNT(word) AS c, word
>   FROM words
>   WHERE part_of_speach=“adjective”
>   AND position IN (SELECT position-1 FROM words WHERE word=“king”)
>   GROUP BY word
>   ORDER BY c DESC;
>
> Since my database includes the words from my entire corpus (60,000,000
> words), I need to further limit my query to document identifiers. Moreover,
> my parts-of-speech are not as simple as “noun”, “verb”, and “adjective”.
> Without going into too much detail, my cool query ultimately looks like
> this:
>
>   SELECT COUNT(word) AS c, word
>   FROM words
>   WHERE did='A05406' AND pos LIKE 'j%'
>   AND (position IN (SELECT position-1 FROM words WHERE (word='king' AND
> did='A05406')))
>   GROUP by word
>   ORDER by c DESC;
>
> In this particular case, kings are described as both earthly and good.
>
> I can then search my index for documents of interest, and then I can feed
> my SQL query an identifier as well as a few words. For example, a humorous
> document on the topic of love describes men and women: [1]
>
>   SELECT COUNT(word) AS c, word FROM words WHERE did='A13520' AND
>   pos LIKE 'j%' AND (position IN (SELECT position-1 FROM words
>   WHERE((LOWER(word)='man' OR LOWER(word)='men' OR
>   LOWER(word)='woman' OR LOWER(word)='women') AND did='A13520')))
>   GROUP BY word ORDER BY c DESC;
>
>   c  word
>   =  =========
>   5  old
>   5  young
>   4  poore
>   3  good
>   2  Old
>   1  Good
>   1  Great
>   1  Rich
>   1  Seruing
>   1  best
>   1  great
>   1  labouring
>   1  little
>   1  painfull
>   1  proper
>   1  singing
>   1  wise
>
> Apparently, when it comes to love, age doesn’t matter, men & women are
> poor, and at the same time, they are not very wise.  ;-)
>
> Fun with SQL. Thank you. Code4lib++
>
> [1] humorous document - http://cds.crc.nd.edu/freebo/A13/A13520/A13520.htm
>
> —
> Eric Morgan
>