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

[kanika11]

yes.. that I am able to do. However I want incident details too

[Adam Stout]

Can you do a scripted web service to give you exactly what you want?   Which API are you using?

[kanika11]

Now I used rest api of service now and by setting sys_param_display as true, I am able to get additional comments. However I just want the last updated comment (order by date). what i am getting now is all the comments concatenated together in a string format.

i am using this

https://<my instance name>.service-now.com/api/now/table/incident?sysparm_query=ORDERBYDESCsys_updated_on^stateIN1,2,3,4,5,6,7,110,113&sysparm_display_value=true&sysparm_limit=20&sysparm_fields=number,state,short_description,comments