Search code examples
sqlurlgroup-bymax

Get max date based on the URL string having multiple records

This is my data, where I need to get last access date of every unique URL.

DATA

AccessDate  URL
20220523    /sites/brazil/sitepages/home.aspx
20220518    /sites/brazil/sitepages/home.aspx
20220621    /sites/turkey/blog.aspx
20220224    /sites/turkey/dashboards.aspx
20220623    /sites/brazil/sitepages/about.aspx
20210629    /sites/usa/service.aspx
20210728    /sites/usa/winodws.aspx
20211117    /sites/turkey/new.aspx
20220513    /sites/brazil/sitepages/home.aspx

There is huge data. I will provide the set of URLs whose last access date I want to get.

INPUT- may in WHERE clause

/sites/brazil/
/sites/usa/
/sites/turkey/

OUTPUT

AccessDate  URL
20220623    /sites/brazil/
20210728    /sites/usa/
20220621    /sites/turkey/
Solution

  • Here's a solution with SQL Server

    select   max(AccessDate)                   as AccessDate
        ,left(url, charindex('/', url, 8)) as url
from     t
group by left(url, charindex('/', url, 8))
    AccessDate url
    2022-06-23 00:00:00.000 /sites/brazil/
    2022-06-21 00:00:00.000 /sites/turkey/
    2021-07-28 00:00:00.000 /sites/usa/

    Fiddle