MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE IF NOT EXISTS `visit` (
  `id` BIGINT(6) NOT NULL AUTO_INCREMENT,
  `siteId` BIGINT(6) NOT NULL,
  `clientId` BIGINT(6) NULL,
  `guest` TINYINT(1) NULL DEFAULT 1,
  `ip` LONGTEXT  NULL,
  `os` LONGTEXT NULL,
  `screen` LONGTEXT  NULL,
  `page` LONGTEXT NULL,
  `referer` LONGTEXT NULL,
  `device` LONGTEXT  NULL,
  `location` LONGTEXT  NULL,
  `created` TIMESTAMP(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`, `siteId`));
INSERT INTO `visit` (`siteId`, `clientId`) VALUES (1, 1);
INSERT INTO `visit` (`siteId`, `clientId`) VALUES (1, 2);
INSERT INTO `visit` (`siteId`, `clientId`) VALUES (1, 1);
INSERT INTO `visit` (`siteId`, `clientId`) VALUES (1, 2);
INSERT INTO `visit` (`siteId`, `clientId`) VALUES (3, 1);
INSERT INTO `visit` (`siteId`, `clientId`) VALUES (3, 1);
INSERT INTO `visit` (`siteId`, `clientId`) VALUES (3, 2);
INSERT INTO `visit` (`siteId`, `clientId`) VALUES (3, 2);
SELECT 
  `id`,
  `siteId`,
  `clientId`,
  `guest`,
  `created`
FROM `visit`


id
siteId
clientId
guest
created


1
1
1
1
2023-05-25 09:20:52.334541

2
1
2
1
2023-05-25 09:20:52.346601

3
1
1
1
2023-05-25 09:20:52.347548

4
1
2
1
2023-05-25 09:20:52.348049

5
3
1
1
2023-05-25 09:20:52.348548

6
3
1
1
2023-05-25 09:20:52.349108

7
3
2
1
2023-05-25 09:20:52.349597

8
3
2
1
2023-05-25 09:20:52.350146


SELECT 
  V.`id`,
  V.`siteId`,
  V.`clientId`,   
  V.`created`
FROM `visit` AS V
  JOIN 
  (
  SELECT 
    `siteId`,
    `clientId`,    
    MAX(`created`) AS `created`
  FROM `visit`
  GROUP BY
    `siteId`,
    `clientId`    
  ) AS VMAX 
  ON 
    V.`siteId` = VMAX.`siteId` AND 
    V.`clientId` = VMAX.`clientId` AND 
    V.`created` = VMAX.`created`


id
siteId
clientId
created


3
1
1
2023-05-25 09:20:52.347548

4
1
2
2023-05-25 09:20:52.348049

6
3
1
2023-05-25 09:20:52.349108

8
3
2
2023-05-25 09:20:52.350146


SELECT 
  V.`id`,
  V.`clientId`,   
  V.`created`
FROM `visit` AS V
  JOIN 
  (
  SELECT 
    `clientId`,
    MAX(`created`) AS `created`
  FROM `visit`
  WHERE
    `siteId` = 1
  GROUP BY
    `clientId`
  ) AS VMAX 
  ON 
    V.`clientId` = VMAX.`clientId` AND 
    V.`created` = VMAX.`created`


id
clientId
created


3
1
2023-05-25 09:20:52.347548

4
2
2023-05-25 09:20:52.348049

id	siteId	clientId	guest	created
1	1	1	1	2023-05-25 09:20:52.334541
2	1	2	1	2023-05-25 09:20:52.346601
3	1	1	1	2023-05-25 09:20:52.347548
4	1	2	1	2023-05-25 09:20:52.348049
5	3	1	1	2023-05-25 09:20:52.348548
6	3	1	1	2023-05-25 09:20:52.349108
7	3	2	1	2023-05-25 09:20:52.349597
8	3	2	1	2023-05-25 09:20:52.350146