Get max of a column in a level of detail group by function-CodePudding

For a combination of product_code,site_name,station_type and created_at, I want to pick the row with the maximum dist_sn.

This is my code.

Select a.* from insight_info a,
(select insight_info.id,product_code, site_name, station_type, 
 created_at = (SELECT DATE(created_at) from insight_info),
 max(dist_sn)
 from insight_info 
 group by product_code, site_name, station_type,insight_info.id,
 created_at= (SELECT DATE(created_at) from insight_info)) b 
 where a.product_code = b.product_code
 and a.site_name = b.site_name
 and a.station_type = b.station_type
 and a.created_at = b.created_at
 and a.product_code ='D00' 
 and a.site_name = 'F00'
 and a.station_type='A00';

This is the error I'm getting. ERROR: column b.created_at does not exist LINE 10: and a.created_at = b.created_at

Without converting the created_at timestamp to date, the query runs without errors. But still doesn't give the desired output. It picked up all the dist_sn instead of just the max in that group.

CodePudding user response：

so all you need to do to fix your error is put an alias in the inner query. So your inner query changes from

 (SELECT DATE(created_at) from insight_info)) b

 (SELECT DATE(created_at) as created_at from insight_info)) b