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
LVL 3
PierellieAsked:
Who is Participating?
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.

satheeshmCommented:
select * from table1 t1
where dateassigned = select max(dateassigned) from table1 t2 where t2.labelid = t1.labelid)
0

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
PierellieAuthor Commented:
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...
0
webvogelCommented:
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

0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

matthewsslCommented:
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
======================
0
scriptlancerinbdCommented:
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
0
racekCommented:
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

0
racekCommented:
@scripta ... he is asking for
GROUP BY DriveID, LabelID ONLY...
0
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
Query Syntax

From novice to tech pro — start learning today.