Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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
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
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 11:38am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

What about this:
 
SELECT TOP 1 * FROM Articles ORDER BY Date DESC
  Permalink  
v2
Comments
Mohamed Mitwalli at 24-Oct-12 19:27pm
   
5+
Marcus Kramer at 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 at 25-Oct-12 1:44am
   
you are right Marcus..
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  
Comments
zlristovski at 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 at 25-Oct-12 2:38am
   
which database and version you are using ?
zlristovski at 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.

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 609
1 OriginalGriff 587
2 Maciej Los 325
3 Mathew Soji 195
4 BillWoodruff 190
0 OriginalGriff 7,356
1 Sergey Alexandrovich Kryukov 6,712
2 DamithSL 5,461
3 Manas Bhardwaj 4,916
4 Maciej Los 4,475


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 25 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100