Link to home
Start Free TrialLog in
Avatar of sjterrell
sjterrellFlag for United States of America

asked on

Most current Records - Query or Query ColdFusion

I have a query object, I need to pull one record for each unique file (basename).  Some files have multiple entries (revisions), I only want the most current based on modified date for each unique file name.

The query structure is attached, from this example, I would expect to get back 11 records. one row for each unique basename, with the most current revision if it applies.
query.png
Avatar of _agx_
_agx_
Flag of United States of America image

Which dbms?  For SQL Server you could use row_number to grab the latest row.  

Note, if it's possible to have multiple records with the exact same "baseName" and "modified" value, add a secondary sorting column to the ORDER BY as a tie breaker

http://sqlfiddle.com/#!3/b5bee

Query
SELECT res.*
FROM   (
        SELECT t.*
             , ROW_NUMBER() OVER (
                  PARTITION BY BaseName ORDER BY Modified DESC
              ) AS RowNum
      FROM   testTable t
) res
where res.RowNum = 1;

Open in new window


Dummy data  (use INT id for simplicity)
create table testTable 
(
  entry_id int identity(1,1)
  , baseName varchar(100)
  , modified datetime
  , otherColumn varchar(100)
)
;

insert into testTable ( baseName, modified, otherColumn )
values 
('AAA', dateAdd(d, -10, getDate()), 'First mod')
,('AAA', dateAdd(d, -3, getDate()), 'Second mod')
, ('AAA', dateAdd(d, -1, getDate()), 'Third mod')
, ('AAA', dateAdd(d, -1, getDate()), 'Fourth mod')
,('BBB', dateAdd(d, -20, getDate()), 'First mod')
, ('BBB', dateAdd(d, -5, getDate()), 'Second mod')
;
   

Open in new window


Result
entry_id 	baseName 	modified 	otherColumn 	RowNum
3 	AAA 	August, 06 2015 20:07:33 	Third mod 	1
6 	BBB 	August, 02 2015 20:07:33 	Second mod 	1

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of LajuanTaylor
LajuanTaylor

@sjterrell - I'm not sure if you have this option available to you, but you could leverage using the "End Date" approach. The caveat is that it would require one or two extra columns added to your database table.

The simplicity of the solution is that you would only have to filter for a document base and where the end date is NULL to get your desired results.

I'm attaching an Excel document and two screen shots where I mimic your data. Note the two fields - "create date" and "end date".

Your db end date field would have a default value of NULL until a document has a new version. The current document end date would remain NULL until and new version is saved...
EndDateExample.xlsx
2015-08-07-2117-WithFilterForBlanks.png
2015-08-07-2118-AllRecordsNoFilter.png
LajuanTaylor makes a good point.  I was assuming you don't have the option of modifying the db. However, if you do - consider his suggestion of using a slightly different table structure instead.  I've used that technique in the past and agree it greatly simplifies this kind of task.
Not sure the reason for the B grade, but to reiterate - unfortunately this kind of query just can't be done easily (or at all) with a QoQ. They're just too limited.  Trying to work around their inherent limitations would require some truly awful and non-performant code.