SELECT *, 'success' as status
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.table_1 group by id order by id asc')
AS ct(ip_address text,"blank" bigint,"306" bigint, "308" bigint, "309" bigint, "310" bigint, "311" bigint)
The above needs to be altered to add a additional column populated with ids which has the highest count value for a particular ip_address Foe example,if a particular ip_address ,the highest count value lies on 306.THen 306 should be in the extra column
| ip_address | code | id |
|---|---|---|
| 27.6 | 20 | 306 |
| 27.6 | 20 | 311 |
| 27.6 | 22 | 306 |
| 27.6 | 40 | 312 |
| 27.6 | 40 | 313 |
| 27.6 | 41 | |
| 27.6 | 41 | 314 |
| 27.6 | 43 | 315 |
| 27.6 | 43 | 316 |
| 27.6 | 42 | 306 |
| 27.62 | 20 | 306 |
| 27.62 | 20 | 310 |
| 27.62 | 20 | 311 |
| 27.62 | 20 | 312 |
| 27.62 | 41 | |
| 27.62 | 41 | 314 |
| 27.62 | 43 | 315 |
| 27.62 | 43 | 316 |
| 27.62 | 45 | 306 |
| 27.62 | 42 | 306 |
| 34.27 | 20 | 306 |
| 34.27 | 50 | 306 |
| 34.27 | 20 | 306 |
| 34.27 | 22 | 306 |
| 34.27 | 20 | 306 |
| 34.27 | 22 | 306 |
| 34.28 | 20 | 306 |
| 34.28 | 42 | 306 |
| 34.69 | 20 | 306 |
| 34.69 | 22 | 306 |
Can we use a CASE expression for this?
