Get record with latest date on SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

SQL

SQL-Server

, +

I have table Articles with next structure:

SQL

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

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

SQL

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

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?

Posted 24-Oct-12 10:38am

zlristovski

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manas Bhardwaj · Answer 1 · 2012-10-24T11:02:00

Solution 1

What about this:

SQL

SELECT TOP 1 * FROM Articles ORDER BY Date DESC

Posted 24-Oct-12 11:02am

Manas Bhardwaj

Updated 24-Oct-12 11:03am

v2

Comments

Mohamed Mitwalli 24-Oct-12 19:27pm

5+

fjdiewornncalwe 24-Oct-12 21:49pm

This won't actually work for the OP because it will only return the top 1 article when the max date from each article is expected.

damodara naidu betha 25-Oct-12 1:44am

you are right Marcus..

damodara naidu betha · Answer 2 · 2012-10-24T19:38:00

Solution 2

Hi..,

I think following query can help you..

SQL

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

Posted 24-Oct-12 19:38pm

damodara naidu betha

Comments

zlristovski 25-Oct-12 2:33am

This is OK, but the problem is in that ROW_NUMBER() is not working in ODBC RDB databese, the syntax is very similar as ORACLE but is not same.

damodara naidu betha 25-Oct-12 2:38am

which database and version you are using ?

zlristovski 25-Oct-12 2:53am

I am using Oracle Rdb Database v7.2 on OpenVMS Opearting System. To connect to the database from Windows app I'm using Odbc driver from Oracle.

Get record with latest date on SQL

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Get record with latest date on SQL

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us