mysql - Common SQL Queston, Finding Counts of a Group By MAX -

mysql - Common SQL Queston, Finding Counts of a Group By MAX -

i'm simple problem, can't seem wrap head around , can't think of keywords up. i'd avoid hacking bad solution when i'm there's efficient way this.

basically, have mysql table comments, have ids , dates submitted, , want users able edit these comments. when comment edited, i'd create new entry in table same id comment beingness edited, new date.

so when i'm selecting list of comments, want utilize select max(datesubmitted) ... grouping id, i'd count of number of ids grouped each one, know how many times comment has been edited.

i think should this:

select id, count(1) "number of edits" comments grouping id;

merged with:

select id, max(`datesubmitted`), comment comments grouping id;

if want latest comment text date edited , count:

select a.id, a. maxdatesubmitted, a.numcomments, b.comment (select id, max(`datesubmitted`) "maxdatesubmitted", count('id') "numcomments" comments grouping id) inner bring together comments b on a.id = b.id , b.datesubmitted = a.maxdatesubmitted;

note: assumes no 2 edits have same date , time (down precision of time portion). in case, think valid assumption.

if want latest edit date , count:

select id, max(`datesubmitted`) "maxdatesubmitted", count('id') "numcomments" comments grouping id

mysql sql group-by