Add the ability to do DISTINCT ON queries

[tirsoh]

Our database is a temporal design because we want to maintain history. So we never update a row and always just insert another row. But at times we want to bring back the most recent record of a given item. We have a digest key that is a hash of a logical key for an item. We then can query by this digest key and say DISTINCT ON digest_key ordered by record_created_on DESC. This gives us the most recent record for that given digest_key.

Table with Temporal Example:

item_id | digest_key | state_code | item_name | price | record_created_on |
1 | 123 | IL | foobar | 12.00 | 09-20-2018 13:45 |
2 | 123 | IL | foobar | 14.00 | 08-20-2018 13:45 |
3 | 123 | IL | foobar | 13.00 | 07-20-2018 13:45 |
4 | 456 | FL | foobar | 16.00 | 09-20-2018 13:45 |
5 | 456 | FL | foobar | 14.00 | 08-20-2018 13:45 |
6 | 456 | FL | foobar | 15.00 | 07-20-2018 13:45 |

When I run the query to get the latest records for each item it would return the following results:

item_id | digest_key | state_code | item_name | price | record_created_on |
1 | 123 | IL | foobar | 12.00 | 09-20-2018 13:45 |
4 | 456 | FL | foobar | 16.00 | 09-20-2018 13:45 |

Maybe there is a way to obtain this result with the current advanced query builder but if there is I would appreciate some instructions on how to get this result. 😄 Otherwise I think this would be a nice feature to add to QueryTree to allow users to do DISTINCT ON type logic.

This is a great tool so far! Keep up the great work!