with b as (SELECT distinct ip_address, FIRST_VALUE (id) OVER (PARTITION BY ip_address ORDER BY count DESC) AS max_id,
FIRST_VALUE (count) OVER (PARTITION BY ip_address ORDER BY count DESC) AS max_count
FROM (SELECT ip_address, id, count(*) FROM public.table_1 where code='200' GROUP BY ip_address,id) as a)
SELECT *, 'success' as status,b.max_id
FROM crosstab(
'SELECT ip_address,id,count(*) as count from table_1 where code= ''200'' group by 1,2 order by 1,2',
'select COALESCE(id,''null'') from public.table1 group by id order by id asc')
AS ct(ip_address text,"blank" bigint,"306" bigint, "308" bigint, "309" bigint, "310" bigint)
The required result is a pivot table with ip_Address as row_identifer, id as column identifier with count as values and status as a added column with success entry and max_id as a column which has id that has the highest count for each respective ip_address
