<div>
Yeah, this is much trickier than it looks (or imo should be, since they should enhance SQL to better handle it :-) ). 
 
In &quot;standard&quot; SQL, you have to use the audit table twice -- once to find the last date for each part, then again to get the ref # corresponding to that date: 
 
 
SELECT Audit.PartID, Audit.Date, Audit.ReferenceNumber, Parts.Quantity 
FROM ( 
SELECT PartID, MAX(Date) AS Date 
FROM Audit 
GROUP BY PartID 
) AS AuditDates 
INNER JOIN Audit ON Audit.PartID = AuditDates.PartID AND 
Audit.Date = AuditDates.Date 
INNER JOIN Parts ON Parts.PartID = AuditDates.PartID
</div>

<div>
My suggested solution is below. &nbsp;Translated into my data model, the SELECT worked for me, so it should work to create a view. &nbsp;Also, I prefer explicit JOINs, so I wrote it that way. 
 
The key is to do two JOINs: the first is needed to determine the latest audit for a particular PartID, using the subselect. &nbsp;The second then merges in the data that you can't aggregate in the first action. 
 
Separately, I'm also making some assumptions about your data model. &nbsp;You attempt to group on both the partID and quantity. &nbsp;I would assume that 'partID' is the primary key of your Parts table, in which case the need to group by Quantity is unnecessary. &nbsp;However, if Quantity is part of the key for Parts, meaning that &quot;partID 2 with quantity 2&quot; and &quot;partID 2 with quantity 3&quot; could both be valid records in the Parts table (at the same time), then you would need to apply the same &quot;GROUP BY B.PartID, B.Quantity&quot; to the end of my solution as well. 
 
This solution also assumes that the (PartID, Date) pair is unique for records in the Audit table. &nbsp;However, if that is not the case, then you would just get multiple records for each ReferenceNumber that has the same last date for a particular PartID. 
 
Finally, this solution will only return the PartIDs which have at least one audit associated with it. &nbsp;If there are any records in the Parts table which do not have any audits associated with them, they would not be displayed in the results of the view. &nbsp;If you would like them as well, just change the &quot;JOIN&quot; to &quot;LEFT JOIN&quot; to correct that. 

<pre><code class="code-1 nolinks" id="code-259515">CREATE VIEW MostRecentTransaction as
 
SELECT B.PartID, B.Quantity, L.LastDate, C.ReferenceNumber
FROM Parts B 
 JOIN (SELECT PartID, max(Date) AS LastDate FROM Audit GROUP BY PartID) AS L
 ON (B.PartID = L.PartID)
 JOIN Audit C ON (L.PartID = C.PartID AND L.LastDate = C.Date)
</code></pre>
</div>

<div>
select b.partid, b.quantity, (select max(&quot;Date&quot;) from audit a where a.partid = b.partid) from parts b;
</div>

<div>
Cminear, I took your suggestion (because I understood what you were doing most) and it worked great. Let me further complicate the problem. What if I need to limit the max date selected to those which have references beginning with X, R, or P? I attempted the following code, which didn't work. 
 
 
&nbsp; 
SELECT B.PartID, B.Quantity, L.LastDate, C.ReferenceNumber 
FROM Parts B 
 
&nbsp; JOIN (SELECT PartID, ReferenceNumber, max(Date) AS LastDate FROM Audit GROUP BY PartID) AS L 
&nbsp; &nbsp; ON (B.PartID = L.PartID) 
 
WHERE ReferenceNumber like '[XRP]%' 
 
&nbsp; JOIN Audit C ON (L.PartID = C.PartID AND L.LastDate = C.Date)
</div>

<div>
Solution 1 was what I was looking for. Very helpful. Thanks so much!
</div>

<div>
<div class="content wysiwyg-content">
Here is a simplified version of my problem. I am trying to create a view using two tables. 
 
In the first table, &quot;Parts&quot; I have 
&nbsp;- PartID 
&nbsp;- Quantity 
 
In the second table, &quot;Audit&quot;, I have 
&nbsp;- PartID 
&nbsp;- Date 
&nbsp;- Reference Number 
 
The two are joined using PartID and there are many audit references for each ID. I would like to select the latest date as well as the associated reference number. I can't seem to figure out the associated reference number part of it. Everytime I try to include it I get all the records from the audit table. I know this is a newbie question and I tried searching for an existing solution but any advice is greatly appreciated. 
 
 
 
 

<pre><code class="code-10 nolinks" id="code-259415">CREATE VIEW MostRecentTransaction as
 
SELECT 
		 B.PartId
		, B.Quantity
		, 'LastDate' = max (c.date)
		
		
	FROM 
		Parts B, Audit C
 
	WHERE 
		B.PartID = C.PartID
 
	GROUP BY B.PartID, B.Quantity
</code></pre>
</div>
</div>

Here is a simplified version of my problem. I am trying to create a view using two tables. 

In the first table, "Parts" I have
 - PartID
 - Quantity

In the second table, "Audit", I have
 - PartID
 - Date
 - Reference Number

The two are joined using PartID and there are many audit references for each ID. I would like to select the latest date as well as the associated reference number. I can't seem to figure out the associated reference number part of it. Everytime I try to include it I get all the records from the audit table. I know this is a newbie question and I tried searching for an existing solution but any advice is greatly appreciated.





Select Related Record in Table Join

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

MySQL Server

PostgreSQL is an object-relational database management system with an emphasis on extensibility and standards-compliance. As a database server, its primary function is to store data securely, supporting best practices, and to allow for retrieval at the request of other software applications. PostgreSQL implements the majority of the SQL2011 standard, is ACID-compliant and transactional using multiversion concurrency control (MVCC), and handles complex SQL queries using many indexing methods that are not available in other databases. It is cross-platform and runs on many operating systems including Linux, FreeBSD, OS X, Solaris, and Microsoft Windows.