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/

CodePudding user response：

SELECT
CASE 
WHEN URL LIKE '/sites/brazil/%' THEN '/sites/brazil/'
WHEN URL LIKE '/sites/usa%' THEN '/sites/usa/'
WHEN URL LIKE '/sites/turkey%' THEN '/sites/turkey/'
END AS URL
, MAX(AccessDate) AS AcessDate
FROM 
table
GROUP BY
1

CodePudding user response：

Here's a solution with Postgres

select   max(accessdate)                                        as AccessDate
        ,array_to_string((string_to_array(url, '/'))[1:3], '/') as url       
from     t
group by array_to_string((string_to_array(url, '/'))[1:3], '/')

Fiddle