How to add the last comment to a SQL query?

[Michael Russell]

I want to create an advanced filter and include the latest comment in the JQL output.

My question is probably little more generic than this - I can't find a way to limit the fields returned in a JQL query.

For example in SQL, I could do SELECT * which returns all fields, or I can do SELECT col1, col2, col3  where only col1, col2, and col3 are returned.

I am looking for a way to do the latter in JQL.

[Nic Brough -Adaptavist- · Accepted solution]

JQL is not SQL and the two can't really be directly compared directly.  If you do though, then think of JQL as being just the "where" clause.  JQL searches don't return any direct data like SQL does, they only return a list of issues that match the question.

The "output" is done by whatever is running the JQL - the code in a gadget for example, gets that list of issues and runs over it looking for the data it needs to build its diagram, a board creates a list of cards to be drawn, most relevant here is probably the "issue navigator" which is a tabular list of the issues, one per row, and with one column per field selected.

The issue navigator lets you choose which fields you want to display in the results.  You can add any field you want to the display, or remove them to simplify it.

So the good news is you can limit the fields if the thing reporting on your results lets you, as does the issue navigator.

The bad news is that there is no "last comment" field for reports to display, unless you've added an app or scripted such a field.  There are some apps that do a "last comment" thing automatically, and some that let you automate or script for it.

[Michael Russell]

This helps confirm a few things. 

I am interested in the last sentence - what apps do a last comment thing?

[Nic Brough -Adaptavist-]

Scriptrunner, the Last Comment app, some of the other field and extension apps, https://marketplace.atlassian.com/search?query=last+comment and others.

[Michael Russell]

Thank you!