将 rownum 与预排序结果一起使用是否良好，还是应该使用rank？
我正在编写一个 Oracle 查询，需要根据事件发生的日期获取最近的事件。
我有两个查询在我的测试用例中似乎运行正常。
第一个使用子查询按日期顺序获取事件，然后我只需通过 rownum 提取第一条记录：
SELECT description FROM
(
SELECT description FROM tablename
WHERE ((event_type IN ('A','I','Y')) AND (meeting_date IS NOT NULL)
AND id='whatever')
ORDER BY meeting_date DESC
)
WHERE rownum = 1
第二个使用排名来完成相同的结果：
SELECT description FROM
(
SELECT description, RANK() OVER( ORDER BY meeting_date DESC) mtg_rank
FROM tablename
WHERE ((event_type IN ('A','I','Y')) AND (meeting_date IS NOT NULL)
AND id= 'whatever')
)
WHERE mtg_rank = 1
对我来说，预排序的 rownum 足够简单，我会使用它。我意识到 rownum 在排序之前起作用，这就是为什么我首先在子选择中进行排序的原因。
但是，我不确定我是否错过了这个想法？
我还想知道在这种情况下排名是否是首选/最佳实践，或者可能更好地传达查询的意图？
I'm writing an Oracle query that needs to get the most recent event based on the date it occurred.
I have two queries that appear to be working correctly in my test cases.
The first uses a subquery to get the events in date order and then I just pull the first record via rownum:
SELECT description FROM
(
SELECT description FROM tablename
WHERE ((event_type IN ('A','I','Y')) AND (meeting_date IS NOT NULL)
AND id='whatever')
ORDER BY meeting_date DESC
)
WHERE rownum = 1
The second one uses rank to accomplish the same result:
SELECT description FROM
(
SELECT description, RANK() OVER( ORDER BY meeting_date DESC) mtg_rank
FROM tablename
WHERE ((event_type IN ('A','I','Y')) AND (meeting_date IS NOT NULL)
AND id= 'whatever')
)
WHERE mtg_rank = 1
To me the presorted rownum is simple enough and I would go with it. I realize that rownum works before a sort which is why I did the ordering in a subselect first.
However, I'm not sure if I'm missing something with that thought?
I am also wondering if rank in this case is the preferred/best practice or perhaps is better at conveying the intent of the query?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
除了平局的情况（Brian 的评论对此进行了很好的讨论）之外，这两个查询都可以工作并且返回相同的结果。
就我个人而言，我更喜欢分析函数方法，因为它的适应性更强。您可以通过在
RANK、DENSE_RANK和ROW_NUMBER分析函数之间交换来调整处理关系的方式。如果您想针对多个ID运行查询，您还可以添加一个PARTITION BY子句来返回每个ID最近的会议> 价值观而不仅仅是一个。Except for cases of ties (which Brian's comment discusses quite well), both queries will work and will return the same results.
Personally, I would much prefer the analytic function approach because it is much more adaptable. You can adjust how ties are handled by swapping between the
RANK,DENSE_RANK, andROW_NUMBERanalytic functions. You can also do things like add aPARTITION BYclause to return the most recent meeting for eachIDif you want to run the query for multipleIDvalues rather than just one.