how to use GROUP BY clause in database views

[kanika11]

i have created one database view to join incident and sys_journal_field table. If i put a condition as t1_element_id=inc_sys_id, then i am getting response. However if we have "y" number of comments on "x" incident, then that "x" incident is coming "y" times in output. My requirement is to get only the latest comment on that incident. so, I thought of using group by clause as t1_element_id=inc_sys_id GROUP BY t1_sys_created_on. But i am getting empty response now. Any suggestions?

Thanks

[Adam Stout]

There is no comments or work notes field?   Are you looking at the default view?   Can you check the columns that are actually on the table (via configure -> table)?

[kanika11]

Hi,

I am able to see all comments and work_notes in web portal. However, when I query incident table using Soap UI, I am not able to see the comments in output.

In the above image, I have shown you the output which I have received while querying incident table. I am getting all the values but the comments are coming as empty.

[Adam Stout]

Is your end goal to get these via an API or in a list view?

[kanika11]

via API

[Adam Stout]

Can you just make a call to the Table API for 1 record sorted by created on desc from sys_journal_field restricted to the comment field on incident?   You'll have more API calls, but you should get what you want.