Max record for a group of recs

livvie

Registered User.
Local time
Today, 16:03
Joined
May 7, 2004
Messages
158
I have a view based on 4 tables. From this view I need to show only the highest revision of an item. The revision number and details are held in one table while the main data is held in another.
This is the code I was using but which doesn't work as I wanted it. I need to only return 1 line per drawing id but this just shows a line for each revision.

SELECT Part_no, Part_Name, CTD_Number, name, MAX(Rev_No) AS Rev_no, Date, Filed, now, DrawingId
FROM SQLACCESS.vwDrawRev
GROUP BY Part_no, Part_Name, CTD_Number, CID, Date, name, Filed, now, DrawingId
ORDER BY Date DESC, now DESC

This is the code that creayes the view
SELECT TOP 100 PERCENT D.CID, D.DrawingId, D.Custpartid AS custpid, D.CTD_Number, C.CustPartid, D.Part_Name, D.Part_no, D.Filed, D.Deleted,
SQLACCESS.tblDrawRev.Rev_No, SQLACCESS.tblDrawRev.EmpID, SQLACCESS.tblDrawRev.Date, SQLACCESS.tblDrawRev.Notes,
SQLACCESS.tblDrawRev.Comments, SQLACCESS.tblctcust.name, SQLACCESS.tblDrawRev.now
FROM SQLACCESS.tblDrawings D INNER JOIN
SQLACCESS.tblCustpart C ON D.Custpartid = C.CustPartid INNER JOIN
SQLACCESS.tblDrawRev ON D.DrawingId = SQLACCESS.tblDrawRev.DrawingId INNER JOIN
SQLACCESS.tblctcust ON D.CID = SQLACCESS.tblctcust.id AND C.CID = SQLACCESS.tblctcust.id
WHERE (D.Deleted = 0)
GROUP BY D.DrawingId, D.Custpartid, D.CTD_Number, C.CustPartid, D.CID, D.Part_Name, D.Part_no, D.Filed, D.Deleted, SQLACCESS.tblDrawRev.Rev_No,
SQLACCESS.tblDrawRev.EmpID, SQLACCESS.tblDrawRev.Date, SQLACCESS.tblDrawRev.Notes, SQLACCESS.tblDrawRev.Comments,
SQLACCESS.tblctcust.name, SQLACCESS.tblDrawRev.now
 
LEFT JOIN the table that contains the revision numbers instead of inner join, that should work.
 
SQL_Hell said:
LEFT JOIN the table that contains the revision numbers instead of inner join, that should work.

No joy I'm afraid same result.
 

Users who are viewing this thread

Back
Top Bottom