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
sjterrellAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
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

_agx_Commented:
UPDATE:

Just reread the title... if you mean you need to use a QoQ (Query of Query), unfortunately your options are limited. QoQ's are extremely basic and do not support either of the examples posted.  Probably the best you can do is something klunky like run 2 more queries:  one to grab the baseName and max date and another to join it back to the 1st query to retrieve the top records.  It's functionally equivalent to the SQL below. However, there's no easy ways to handle the "multiple records with the same baseName + modified date scenario.  

IMO you're much better off using one of the db query's posted instead.


ORIGINAL

For other dbms's, something more generic like this should work.  Again, if it's possible to have multiple records with the same BaseName and Modified date, you'll have to filter on another column as well to break the tie

SELECT det.*
FROM   YourTable det INNER JOIN 
         (
            SELECT BaseName, MAX(Modified) AS Modified
            FROM   YourTable 
            GROUP BY BaseName
         ) 
         mx ON mx.BaseName = det.BaseName 
               AND mx.Modified = det.Modified

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LajuanTaylorCommented:
@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
_agx_Commented:
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.
_agx_Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.