SELECT
tmp_sample.id,
tmp_sample.word,
tmp_sample.lemma,
tmp_sample.syllables,
tmp_sample.synsetid,
tmp_sample.definition,
sentences.sentence,
tmp_sample.pos,
(SELECT Avg(tmp.lexile) FROM `tmp_sample` AS tmp WHERE tmp.lemma=tmp_sample.lemma and tmp.synsetid=tmp_sample.synsetid) AS lex_avg,
tmp_sample.lexile,
abs(tmp_sample.lexile-(SELECT Avg(tmp.lexile) FROM `tmp_sample` AS tmp WHERE tmp.lemma=tmp_sample.lemma and tmp.synsetid=tmp_sample.synsetid)) AS abs_val,
tmp_sample.title,
tmp_sample.`subject`,
tmp_sample.frequency,
tmp_sample.passageid,
tmp_sample.sentenceid,
tmp_sample.source
FROM
tmp_sample
INNER JOIN sentences ON tmp_sample.sentenceid = sentences.id
WHERE tmp_sample.passageid=478
GROUP BY
tmp_sample.lemma,
tmp_sample.synsetid
ORDER BY
abs_val,
tmp_sample.lemma


/* passage id's
159
160
161
162
163
179
180
186
192
468
477
478
479
480
481
597
598
599
600
601
704
706
708
711
713
765
766
767
768
770
*/