How to get unique data in mysql 8.0 +

I am trying to do a simple select query that will filter out duplicate data just like what was possible in mysql versions below 5.6.

I have done a lot of research online and i have tried everything that i have found but i still don't seem to get the gist.

CREATE TABLE Test (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  Comment text,
  commentOnId int(11) DEFAULT NULL,
  Time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into test (comment, commentonid) 
VALUES
('My first comment', null),
    ('reply 1',1),
    ('reply 2',1);`

select id, comment, commentonid from Test group by commentonid;
select id, comment, distinct commentonid from Test group by commentonid;
select id, comment, distinct commentonid from Test;

select id, comment, ANY_VALUE(commentonid) as unique from Test group by unique;
select id, comment, distinct ANY_VALUE(commentonid) from Test;`

`select id, comment, MIN(commentonid) from Test;

Every single one of the select queries fail or at the very least don't get the desired result.

I want to get a unique commentOn field as there are two with the value of 1. What is the correct select statement to use in order to get unique data in mysql 8.0?

Solution

You're using the aggregation function on the wrong column. You don't use ANY_VALUE() on the column you're grouping by, you use it on all the other columns that aren't uniquely determined by that column.

SELECT ANY_VALUE(id) AS id, ANY_VALUE(comment) AS comment, commentonid
FROM Test
GROUP BY commentonid

Note that this doesn't guarantee that id and comment will come from the same row. You could get

1  reply 2  1

as the result. If you want to get consistent a consistent row, you normally should specify some criteria for which row you want in the group, such as the highest id. See SQL select only rows with max value on a column for how to write such queries.