sjterrell
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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
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.
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
Open in new window
Dummy data (use INT id for simplicity)
Open in new window
Result
Open in new window