按某个字段分组然后从每组取出某个字段最大的一条纪录

记录一下有下面函数可以使用。

SELECT
 *        
FROM
 (
		SELECT *, 
			ROW_NUMBER () OVER (PARTITION BY package_name ORDER BY version_code DESC) rn 
		FROM va_file            
	) T        
WHERE rn = 1

 

以及一下子就能想到的：

SELECT
    vf.*
FROM
    va_file vf
JOIN (
    SELECT
        package_name,
        MAX (version_code) AS version_code
    FROM
        va_file
    WHERE
        status = 2
    GROUP BY
        package_name
) tmp ON vf.package_name = tmp.package_name
AND vf.version_code = tmp.version_code

 

类似的需求有：

1、查找用户每天第一次登录的时间

-- 查找用户每天第一次登录的时间
select * FROM
(
	select user_id, create_datetime,  
		ROW_NUMBER () OVER (PARTITION BY to_char(create_datetime,'yyyy-MM-dd') ORDER BY create_datetime ASC) rn  
	from user_login_log ORDER BY user_id
) t
where rn = 1

 

2、继而拓展出类似的需求：根据用户登录记录表查询用户连续登录天数

select 
	user_id,
	min(create_datetime) start_date,
	max(create_datetime) end_date,
	count(1) running_days

from (
	select 
		user_id,
		create_datetime,
		date_part('day',create_datetime::timestamp - timestamp'2017-01-01') day_interval,
		row_number() over(partition by user_id order by to_char(create_datetime,'yyyy-MM-dd')) day_rank,
		(
			row_number() over(partition by user_id order by to_char(create_datetime,'yyyy-MM-dd'))
		) - (
			date_part('day',create_datetime::timestamp - timestamp'2017-01-01')
		) diff_value
	from (
			select * FROM (
					select user_id, 
								create_datetime,  
								ROW_NUMBER () OVER (PARTITION BY to_char(create_datetime,'yyyy-MM-dd') ORDER BY create_datetime) rn  
					from user_login_log
			) t
			where rn = 1
	) t
) t 
GROUP BY user_id, diff_value
order by user_id, start_date
;