Some light SQL to finish your day.

One of the more interesting queries I worked on yesterday was this:

SELECT uuid, aenttypename, attributename, coalesce(vocabname, measure, freetext) as responce, vocabid, attributeid

FROM (SELECT uuid, attributeid, aenttypename

FROM (SELECT aenttypeid, attributeid, aenttypename

FROM idealaent join aenttype using (aenttypeid)

WHERE isIdentifier = ‘true’)

JOIN (SELECT uuid, aenttypeid

FROM archentity

WHERE deleted is null

GROUP BY uuid

HAVING max(aenttimestamp)

LIMIT 3

OFFSET 0) USING (aenttypeid)

)

JOIN aentvalue USING (uuid, attributeid)

JOIN attributekey using (attributeid)

LEFT OUTER JOIN vocabulary USING (vocabid, attributeid)

GROUP BY uuid, attributeid

HAVING max(valuetimestamp);

</code>

We use it to make something like:

[gallery link=”file”]

Be tantalized.

Dialogue & Discussion