sql - Distinct selection by one of values with a condition
问题描述
I have a table with the columns: id, relation_id, someData, created_at
I need to select all rows (with all columns, basically SELECT *) that have distinct relation_id AND have the latest created_at date for this specific relation_id.
Example data:
id, relation_id, someData, created_at
1, 123, I am data, 2020-01-01
2, 123, I am data, 2019-01-01
3, 321, I am also data, 2015-01-01
4, 555, Data, 2020-06-05
5, 555, Data, 2011-01-05
Example output:
1, 123, I am data, 2020-01-01
3, 321, I am also data, 2015-01-01
4, 555, Data, 2020-06-05
解决方案
please use below SQL. Row_number window function will rank the data based on order of created_at. filter row number =1 will pick latest row. You can tweak this to choose whatever you want.
SELECT
id ,
relation_id,
someData ,
created_at
FROM
(
SELECT
id ,
relation_id,
someData ,
created_at ,
row_number() OVER (PARTITION BY
id, relation_id ORDER BY
id, relation_id, created_at DESC ) rn
FROM
TABLE) rs
WHERE
rs.rn=1
推荐阅读
- javascript - 如何在状态 React JS 中渲染元素?
- java - 如何将联系人从 Android 同步到 Web 服务器
- javascript - 在 Google AppMaker 中发送电子邮件时出错
- java - 动态 Web 项目中的 HibernateException
- c++ - 为什么即使只是 RGB 到灰度转换 (Vivado HLS),延迟也会太高?
- google-cloud-storage - 如何识别数据流作业中已处理的文件
- javascript - chart.js 定位器在哪里使工具提示向下移动
- html - h4 边距的 CSS 无效属性值
- c# - 如果调用(运行所有测试),则单元测试异步等待方法断言失败,但如果我一个接一个地运行,则测试通过
- c# - 无法按文本值检查元素