获取有关SQL的最新日期的记录 [英] Get record with latest date on SQL

问题描述

我有具有以下结构的表格文章:

I have table Articles with next structure:

ArticleID Price Date
1         50    2010-12-23
1         52    2012-10-22
2         102   2009-02-06
2         95    2007-05-30
2         89    2011-09-12
3         41    2006-01-21
3         63    2012-03-16

我只想获取最新日期的文章，这是通过下一个SQL查询实现的:

I want to get just the articles with latest date, this is implemented with the next SQL query:

SELECT A1.* FROM (SELECT ArticleID, MAX(Date) AS Latest FROM Articles GROUP BY ArticleID) A2, Articles A1 WHERE A1.ArticleID = A2.ArticleID AND A1.Date =  Latest

查询工作正常，但是从表中检索数据的速度非常慢，我在表中有10万条记录.问题是:可以优化此查询以更快地从数据库中读取吗?

The query is working fine, but the retrieving data from the table is going very slow, I have more than 100 000 records in the table. The question is: Can be optimized this query for faster reading from database?

推荐答案

这是怎么回事:

What about this:

SELECT TOP 1 * FROM Articles ORDER BY Date DESC

您好，..

我认为以下查询可以为您提供帮助.

Hi..,

I think following query can help you..

SELECT A.ArticleId,A.Cost,A.Date FROM 
  (
   SELECT ArticleId,Cost,Date,
   ROW_NUMBER() OVER(PARTITION BY ArticleId ORDER BY Date DESC) ROWNUM 
   FROM Articles
   ) AS A
WHERE A.ROWNUM = 1 

谢谢

Thank you

这篇关于获取有关SQL的最新日期的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！