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]

Database Views in ServiceNow do not support aggregates.   Database views

Doesn't the list view only show the latest comment anyway?   I have handled similar requirements by adding a reference field on incident to the table I need to get the latest value from and adding a BR to that table to update incident when a new value is added.   However, if you are doing this on sys_journal, that may not be ideal.

There may be something you can do with a calculated field to pull the latest comment in a field that you can add to the list view if it isn't displaying the way you want.

[kanika11]

when I query just the incident table, I don't get comments in output. I get the field

<comments_and_work_notes>, however it is coming empty.

when I created the database view using incident and sys_journal_field table, though i get comments in output but not in the required format. You mentioned that you have handled similar requirements by adding a reference field on incident to the table I need to get the latest value from, So, can you please guide me with the steps so that while querying incident table, I can get latest additional comments too.

Thank You for your timely reply!!

[Adam Stout]

In my OOTB incident table, I have three fields:

1) Work notes (work_notes) which contains just the work notes.

2) Additional comments (comments) which contains just the comments.

3) Comments and Work notes (comments_and_work_notes) which contains both.  

If you just display Additional comments, do you get what you are looking for?

[kanika11]

if i query the incident table, I get <comments_and_work_notes/> which are coming as empty. Can you please tell me what is OOTB table and how to query it.

Thanks