MySQL 5.5

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

CREATE TABLE `visit_record` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `date` VARCHAR( 19 ) NOT NULL,
    `url` VARCHAR( 1024 ) NOT NULL,
    EXTRA_COLUMNS TEXT,
    PRIMARY KEY ( `id` )
);
insert into visit_record values
 (1,'2023-06-21 00:00:00','https://example1.com/en','...')
,(2,'2023-06-20 23:00:00','https://example1.com/uk','...')
,(3,'2023-06-20 14:00:00','https://example2.com/en/about','...')
,(4,'2023-06-21 03:00:00','https://example2.com/fr','...')
;
select  * from visit_record;

Records: 4  Duplicates: 0  Warnings: 0




id
date
url
EXTRA_COLUMNS


1
2023-06-21 00:00:00
https://example1.com/en
...

2
2023-06-20 23:00:00
https://example1.com/uk
...

3
2023-06-20 14:00:00
https://example2.com/en/about
...

4
2023-06-21 03:00:00
https://example2.com/fr
...


SELECT
    `visit_record`.`id`,
    `visit_record`.`date`,
    SUBSTRING_INDEX(
      SUBSTRING_INDEX( 
        SUBSTRING_INDEX( 
         SUBSTRING_INDEX( `visit_record`.`url`, '/', 3 )
           , '://', -1 )
         , '/', 1 )
       , '?'
  , 1 ) AS `domain`
FROM
    `visit_record`;


id
date
domain


1
2023-06-21 00:00:00
example1.com

2
2023-06-20 23:00:00
example1.com

3
2023-06-20 14:00:00
example2.com

4
2023-06-21 03:00:00
example2.com


select * 
from(
  select *,row_number()over(partition by domain order by date desc) rn
  from (
    SELECT *,
       SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING_INDEX( `visit_record`.`url`, '/', 3 ), '://', -1 ), '/', 1 ), '?', 1 ) AS `domain`
    FROM  `visit_record`
   )t1
  )t2
where rn=1;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by domain order by date desc) rn
  from (
    SELECT *,
       SUBSTR' at line 3


select * 
from (
    SELECT *,
       SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING_INDEX( `visit_record`.`url`, '/', 3 ), '://', -1 ), '/', 1 ), '?', 1 ) AS `domain`
    FROM  `visit_record`
   )t1 
inner join (
  select domain,max(date) lastdate
  from (
    SELECT *,
       SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING_INDEX( `visit_record`.`url`, '/', 3 ), '://', -1 ), '/', 1 ), '?', 1 ) AS `domain`
    FROM  `visit_record`
   ) t0
  group by domain
  )t2 on t1.domain=t2.domain and t1.date=t2.lastdate
;


id
date
url
EXTRA_COLUMNS
domain
domain
lastdate


1
2023-06-21 00:00:00
https://example1.com/en
...
example1.com
example1.com
2023-06-21 00:00:00

4
2023-06-21 03:00:00
https://example2.com/fr
...
example2.com
example2.com
2023-06-21 03:00:00


select vr1.id, vr2.`date`, vr1.URL
from visit_record vr1
join (
    select max(rec.`date`) as `date`
    from visit_record rec
) vr2
on 1=1
order by id desc
limit 0, 1


id
date
URL


4
2023-06-21 03:00:00
https://example2.com/fr

id	date	url	EXTRA_COLUMNS
1	2023-06-21 00:00:00	https://example1.com/en	...
2	2023-06-20 23:00:00	https://example1.com/uk	...
3	2023-06-20 14:00:00	https://example2.com/en/about	...
4	2023-06-21 03:00:00	https://example2.com/fr	...

id	date	domain
1	2023-06-21 00:00:00	example1.com
2	2023-06-20 23:00:00	example1.com
3	2023-06-20 14:00:00	example2.com
4	2023-06-21 03:00:00	example2.com