Discuss "Select Top 1 Unique Record" in the SQL Development forum.

BLaaaaaaaaaarche · #1 April 16th, 2008, 08:48 AM

Okay, I need some help with a SQL query. I have a DB with the following:

Quote:

Table: tblManuals
Fields: ManualID, ManualTypeID, ProductID, ManualPartNumber, ManualDescription, ManualRevision

For each manual, it could have multiple revisions. The way I identify each manual is by the part number, type ID, and product ID. How do I select just the MOST RECENT manual for each revision (in other words, just the most recent revision of the manual).

Some examples of data include:

1, 1, 10, Install Guide, 1
2, 1, 10, Install Guide, 2
3, 2, 12, Service Manual, 1
4, 2, 12, Service Manual, 2
5, 2, 12, Service Manual, 3
6, 1, 10, Install Guide, 3

So, the SQL would just select records 5 and 6, since those are unique manual types and are the most recent revisions.

jmurrayhead · #2 April 16th, 2008, 09:00 AM

See if this works:

Code:

SELECT DISTINCT(ManualTypeID), 
    ManualID, 
    ProductID, 
    ManualPartNumber, 
    ManualDescription, 
    ManualRevision
FROM tblManuals
ORDER BY ManualID DESC

BLaaaaaaaaaarche · #3 April 16th, 2008, 09:06 AM

That will not work because several different manuals can have the same ManualTypeID. I wonder if I just select based on unique ManualPartNumber? I don't think anyone can enter a duplicate Part Number... hmmm. Thanks for the clue.

mehere · #4 April 16th, 2008, 09:07 AM

use MAX and GROUP BY

Code:

SELECT ManualTypeID, ProductID, ManualPartNumber, ManualDescription, MAX(ManualRevision) FROM tblManuals GROUP BY ManualTypeID, ProductID, ManualPartNumber, ManualDescription

if you need to also get the ManualID ... you would have to go another way by using a subselect ... let me know if that's how you need to go.

Wolffy · #5 April 16th, 2008, 09:55 AM

The problem comes if you need the ManualID for some reason, since if you included that in your GROUP BY, you get every record (natch). To get the full record:

Code:

SELECT a.* FROM tblManuals A
JOIN 
  (SELECT ManualTypeId, Max(ManualRevision) as Latest
     FROM tblManuals
     GROUP BY ManualTypeId) B
  ON (a.ManualTypeId = b.ManualTypeId 
        AND a.ManualRevision = b.Latest)

BLaaaaaaaaaarche · #6 April 16th, 2008, 10:07 AM

Okay, I am running into some difficulties. Here is my original SQL that grabs EVERY revision for each ManualPartNumber:

Code:

strSQL = "SELECT m.ManualID, m.ManualName, m.ManualApplication, m.ManualPartNumber, m.ManualSerialStart, m.ManualSerialEnd, m.ManualRevision, " & _
	"m.ManualIteration, m.ManualPublished, m.ManualPublishedDate, u.UserName, p.ProductName, mt.ManualTypeName " & _
	"FROM tblManuals m " & _
	"LEFT JOIN tblUsers u ON m.ManualPublisher = u.UserID " & _
	"LEFT JOIN tblProducts p ON m.ProductID = p.ProductID " & _
	"LEFT JOIN tblManuals_types mt ON m.ManualTypeID = mt.ManualTypeID " & _
	"WHERE 1 = 1" & _
	strProdID & _
	strManualTypeID

I keep on getting an error when trying to add the MAX to the query. Basically, I just need to select the MAX ManualRevision for each unique ManualPartNumber.

jmurrayhead · #7 April 16th, 2008, 10:13 AM

You need to add the Group By clause when using MAX.

BLaaaaaaaaaarche · #8 April 16th, 2008, 10:27 AM

Yes, but that means I need to GROUP BY all fields... right?

jmurrayhead · #9 April 16th, 2008, 10:36 AM

Quote:

Originally Posted by BLaaaaaaaaaarche

Yes, but that means I need to GROUP BY all fields... right?

I would think you would only need to group by ManualPartNumber in this case.

mehere · #10 April 16th, 2008, 11:04 AM

you need to use all fields listed in your SELECT statement that are not part of an aggregate function (ie: MAX, SUM, COUNT, etc) in your GROUP BY statement.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Connection String & Record Set Functions	mehere	ASP Code Samples	0	March 21st, 2008 12:07 PM

Discuss "Select Top 1 Unique Record" in the SQL Development forum.

SQL Development - Structured Query Language (SQL) is the talk of databases. Discuss topics such as joins, triggers and other advanced SQL topics.