asked on

SQL Query to return records by latest date.

I have an access database application I use to track hard drive status and what we use them for. I want to create a report, generated from a query, that will pull only the most recently updated record for our hard drives. I designed the database in a way that it allows us to log the previous "statuses" of our hard drives. For example, below, you can see on the top and bottom there are two seperate entries for the hard drives labeled "2500" and "2501" the only difference in those records are the dates. (there are more fields in the table, for comments and the actual "status" that we're assigning the drives - those will differentiate as well).

What would the proper SQL statement be to pull all the records, and for records that contain the same DriveID and LabelID only return the most recent record occording to the "DateAssigned" field?

DriveID      LabelID      ManufacturerID      ModelID       DateAssigned
WCAU4783      2500      Western Digital      WD10EADS       5/21/2009
WCAU4784      2501      Western Digital      WD10EADS       5/21/2009
WMATV063      2502      Western Digital      WD1002FBYS      5/21/2009
WMATV105      2503      Western Digital      WD1002FBYS      5/21/2009
WMATV118      2504      Western Digital      WD1002FBYS      5/21/2009
WMATV110      2505      Western Digital      WD1002FBYS      5/21/2009
WMATV681      2506      Western Digital      WD1002FBYS      5/21/2009
WMATV112      2507      Western Digital      WD1002FBYS      5/21/2009
WMATV165      2508      Western Digital      WD1002FBYS      5/21/2009
WMATV118      2509      Western Digital      WD1002FBYS      5/21/2009
WCAU4783      2500      Western Digital      WD10EADS       5/22/2009
WCAU4784      2501      Western Digital      WD10EADS       5/22/2009

ASKER CERTIFIED SOLUTION

satheeshm

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pierellie

ASKER

This is what I have already in the query....

SELECT Drive.DriveID, Drive.LabelID, Manufacturer.ManufacturerID, Model.ModelID, DriveUseTransaction.DateAssigned, DriveUseTransaction.Comment, DriveUse.DriveUseID
FROM Model INNER JOIN (Manufacturer INNER JOIN (DriveUse INNER JOIN (Drive INNER JOIN DriveUseTransaction ON Drive.DriveKey=DriveUseTransaction.DriveKey) ON DriveUse.DriveUseKey=DriveUseTransaction.DriveUseKey) ON Manufacturer.ManufacturerKey=Drive.ManufacturerKey) ON Model.ModelKey=Drive.ModelKey

unfortunatly i've only been working with SQL for a couple days now... and this query was generated from a wizard...

webvogel

Not sure, if this will work. With Drive.DriveKey or an other id you can than work with your query put
WHERE Drive.DriveKey = '$var'
at the end.

SELECT DISTINCT(CONCAT(Drive.DriveID, ' ', Drive.LabelID)), MAX(DriveUseTransaction.DateAssigned), Drive.DriveKey FROM Drive
INNER JOIN DriveUseTransaction ON Drive.DriveKey=DriveUseTransaction.DriveKey

Open in new window

matthewssl

Since the only field that varies is DateAssigned, wouldn't this get what you need?
=====================
Select DriveID, LabelID, ManufacturerID, ModelID, Max(DateAssigned) as MostRecent
from vwDriveInfo
Group by DriveID, LabelID, ManufacturerID, ModelID
======================
vwDriveInfo would be the Query that pulls those fields. e.g.
======================
Create View vwDriveInfo as
SELECT Drive.DriveID, Drive.LabelID, Manufacturer.ManufacturerID, Model.ModelID, DriveUseTransaction.DateAssigned, DriveUseTransaction.Comment, DriveUse.DriveUseID
FROM Model INNER JOIN (Manufacturer INNER JOIN (DriveUse INNER JOIN (Drive INNER JOIN DriveUseTransaction ON Drive.DriveKey=DriveUseTransaction.DriveKey) ON DriveUse.DriveUseKey=DriveUseTransaction.DriveUseKey) ON Manufacturer.ManufacturerKey=Drive.ManufacturerKey) ON Model.ModelKey=Drive.ModelKey
======================

scriptlancerinbd

Hi!

It seems that you're trying something in MS Access. I've plotted the same in MS Access and achieved the result you're asking for.

==========
SELECT DriveID, LabelID, ManufacturerID, ModelID, max(DateAssigned)
FROM test
GROUP BY DriveID, LabelID, ManufacturerID, ModelID;
==========

Very simple, see.

Regards,

--S

racek

in ...(SELECT d.DriveID, d.LabelID, dt.DateAssigned, ManufacturerID, ModelID...
I completed ManufacturerID, ModelID with table prefix mf.ManufacturerID, m.ModelID
(I think it is correct)
otherwise send me an error message

SELECT x.DriveID,x.LabelID, y.ManufacturerID, y.ModelID, x.da DateAssigned
FROM
(SELECT d.DriveID, d.LabelID, dt.DateAssigned, mf.ManufacturerID, m.ModelID
FROM Model m
INNER JOIN Drive d ON m.ModelKey=d.ModelKey
INNER JOIN Manufacturer mf ON mf.ManufacturerKey=d.ManufacturerKey
INNER JOIN DriveUseTransaction dt ON d.DriveKey=dt.DriveKey  
INNER JOIN DriveUse du ON du.DriveUseKey=dt.DriveUseKey ) AS y
JOIN
(SELECT d.DriveID, d.LabelID, MAX(dt.DateAssigned) da 
 FROM Drive d 
 INNER JOIN DriveUseTransaction dt ON d.DriveKey=dt.DriveKey 
 GROUP BY 1,2) AS x
on x.DriveID = y.DriveID
and x.LabelID = y.
AND x.da = y.DateAssigned;

Open in new window

racek

@scripta ... he is asking for
GROUP BY DriveID, LabelID ONLY...