0

As of the example below, I have 3 rows and as of now, it prints all rows.

enter image description here

I want my SQL query to show all data, but grouping them by id_unique. If id_unique are the same, group them and show the latest data for that id_unique.

I have tried SELECT DISTINCT id_unique FROM ... which works perfectly, but if I add other columns to the SELECT like this: SELECT DISTINCT id_unique, subject, cover_hash FROM ..., it does not combines the values for id_unique if they are the same ones for each row.

I have also tried SELECT * FROM ... GROUP BY id_unique ... but I get ... which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by so I tried to add id so it would be like this: GROUP BY id, id_unique. But! Same result as with DISTINCT: the rows are not smashed into one.

I have no idea of how to go from here.

CREATE TABLE `posts` (
  `id` int NOT NULL,
  `id_unique` varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `subject` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `cover_hash` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `cover_url` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `cover_owner` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `cover_owner_url` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `content_beforebreak` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `content_afterbreak` text,
  `tags` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `hasbeencorrected` tinyint DEFAULT NULL,
  `timestamp_saved` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `timestamp_published` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `timestamp_edited` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `is_pinned` tinyint DEFAULT NULL,
  `is_inenglish` tinyint DEFAULT NULL
) ENGINE=InnoDB;

INSERT INTO `posts` (`id`, `id_unique`, `subject`, `cover_hash`, `cover_url`, `cover_owner`, `cover_owner_url`, `content_beforebreak`, `content_afterbreak`, `tags`, `hasbeencorrected`, `timestamp_saved`, `timestamp_published`, `timestamp_edited`, `is_pinned`, `is_inenglish`) VALUES
(1, '60ca427cc3323', 'asd 2', NULL, NULL, NULL, NULL, 'test', NULL, 'dsa2', NULL, NULL, '1623868020', NULL, NULL, NULL),
(2, '60ca427cc3323', 'Det här är bara ett test', NULL, NULL, NULL, NULL, 'Hej och hå för bövelen!', NULL, 'dsa,test', NULL, NULL, '1623868020', '1623868345', NULL, NULL),
(4, '60ca427cc3323', 'Det här är bara ett test', NULL, NULL, NULL, NULL, 'Hej och hå för bövelen!', 'Ännu mer testnade :)', 'haha,hihi,hoho,hehe', NULL, NULL, '1623868020', '1623869261', NULL, NULL);

SELECT *
FROM posts
WHERE timestamp_saved IS NULL
AND is_pinned IS NULL
AND is_inenglish IS NULL
AND FROM_UNIXTIME(timestamp_published) < NOW()
GROUP BY id
ORDER BY timestamp_edited DESC
LIMIT 0, 10
Airikr
  • 6,258
  • 15
  • 59
  • 110
  • 1
    Why does a column called `id_unique` have repeated values? – Gordon Linoff Jun 16 '21 at 22:31
  • @GordonLinoff To be able to identify the blog post. Only the post with `timestamp_edited IS NULL` are the original blog post. The other posts are changes to that post. I could do a `id_post`, but I want to see how "easy" this method is. @astentx Perhaps. Thanks :) I will take a closer look. – Airikr Jun 17 '21 at 07:42
  • @Airikr . . . If the linked post answers your question, please delete this answer (you can close it as a duplicate). – Gordon Linoff Jun 17 '21 at 12:38
  • Am sorry, @GordonLinoff. I haven't taken a closer look at it yet because I haven't had any time for it. Will take a closer look when I have more time :) – Airikr Jun 17 '21 at 12:56

0 Answers0