Group By and Aggregation issue to pick up latest value for certain column- PSQL-CodePudding

I am trying to generate new table from existing dataset in postgres db as below, hope someone can assist on my query to generate new table that takes latest value from url 1 and gets remaining columns group by

available table :

name	type	url1	url2	date	days
First	online	http1	abclink1	2022-01-01	1
First	online	http2.	abclink1	2022-01-25	4
Second	offline	http10.	xyzlink232r4	2022-01-12	1

new table needed is as below - First, group by should only be applicable on name and type and give http2 for url1 as that is the latest value based on date.

name	type	url1	url2	date	Max days
First	online	http2.	abclink1	2022-01-25	4
Second	offline	http10.	xyzlink232r4	2022-01-12	1

Tried with group by name, type, url1, url2 and it returns same 3 rows as they form separate group due to varying url1 for same name. None of the columns are unique in this table.

select name, type, url1, url2,max(date), max(days) from table
group by name, type, url1, url2

~pk

CodePudding user response：

SELECT *
FROM(
    SELECT name,
           type,
           url1,
           url2,
           date,
           days,
           rank() over (partition by name, type order by days desc, date desc) as rank
    FROM xyz
) tab
WHERE rank = 1;