clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1044276 fiddles created (9440 in the last week).

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`));
 hidden batch(es)


INSERT INTO `visit` (`siteId`, `clientId`) VALUES (1, 1);
 hidden batch(es)


INSERT INTO `visit` (`siteId`, `clientId`) VALUES (1, 2);
 hidden batch(es)


INSERT INTO `visit` (`siteId`, `clientId`) VALUES (1, 1);
 hidden batch(es)


INSERT INTO `visit` (`siteId`, `clientId`) VALUES (1, 2);
 hidden batch(es)


INSERT INTO `visit` (`siteId`, `clientId`) VALUES (3, 1);
 hidden batch(es)


INSERT INTO `visit` (`siteId`, `clientId`) VALUES (3, 1);
 hidden batch(es)


INSERT INTO `visit` (`siteId`, `clientId`) VALUES (3, 2);
 hidden batch(es)


INSERT INTO `visit` (`siteId`, `clientId`) VALUES (3, 2);
 hidden batch(es)


SELECT `id`, `siteId`, `clientId`, `guest`, `created` FROM `visit`
id siteId clientId guest created
1 1 1 1 2019-12-09 13:11:07.638740
2 1 2 1 2019-12-09 13:11:07.646723
3 1 1 1 2019-12-09 13:11:07.652981
4 1 2 1 2019-12-09 13:11:07.655342
5 3 1 1 2019-12-09 13:11:07.658656
6 3 1 1 2019-12-09 13:11:07.661426
7 3 2 1 2019-12-09 13:11:07.663128
8 3 2 1 2019-12-09 13:11:07.666023
 hidden batch(es)


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 2019-12-09 13:11:07.652981
4 1 2 2019-12-09 13:11:07.655342
6 3 1 2019-12-09 13:11:07.661426
8 3 2 2019-12-09 13:11:07.666023
 hidden batch(es)


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 2019-12-09 13:11:07.652981
4 2 2019-12-09 13:11:07.655342
 hidden batch(es)