Returning Row with the Max Value in a JOIN

[zyman10]

I am joining two tables together. First table (Parts) contains

PartID, PartName

Second table (Products) contains

ProductID, PartID, Title

The Parts Table contains unique Parts so when I do a select on it, no duplicates are returned. However, when I do something like this

SELECT Parts.PartID, Parts.PartName, ProductID, Products.Title
FROM Parts INNER JOIN Products ON Parts.PartID = Products.PartID

I get a table back with unique ProductIDs, but the same PartID is repeated several times for different ProductIDs. For example a snippet of my results are below:

PartID ---- PartName ---- ProductID ---- Title ----
.....
23 -------- GOG-Green ---- 46 ---------- Goggles Kit 1
23 -------- GOG-Green ---- 47 ---------- Goggles Kit 2
23 -------- GOG-Green ---- 58 ---------- Goggles Kit 3
.....

I want to only get the record here with the highest Product ID. So I would want to only return
23 -------- GOG-Green ---- 58 ---------- Goggles Kit 3

Does anyone know an easy way to do this?

I tried this:

SELECT Parts.PartID, Parts.PartName, [red]MAX(ProductID)[/red], Products.Title
FROM Parts INNER JOIN Products ON Parts.PartID = Products.PartID

But it gave me an error. I'm sure I'm close but a google search didn't return much....

So I know it may be bad database structure but I can't really do much about it at the moment.

 

[markros]

There are two blogs that can help you with the problem:

http://blogs.lessthandot.com/index....rver/including-an-aggregated-column-s-related

and

http://blogs.lessthandot.com/index....gn/including-an-aggregated-column-s-related-2

One of the solutions to your problem would be

select Parts.PartID, Parts.PartName, Products.ProductID, Products.Title
FROM Parts INNER JOIN Products ON Parts.PartID = Products.PartID inner join (select Max(ProductID) as MaxProduct, PartID from Products group by PartID) X on Parts.PartID = X.PartID and Products.ProductID = X.ProductID

you can view other 6 different solutions of this common problem in the referenced blogs.

 

[zyman10]

Ok, great, that helped.

The query you wrote made gave me something like this when I used it:

....
23 -------- GOG-Green ---- 58 ---------- Goggles Kit 3
23 -------- GOG-Green ---- 58 ---------- Goggles Kit 3
23 -------- GOG-Green ---- 58 ---------- Goggles Kit 3
....

So then I just did a second query to get the TOP 1 of each PartID. Not sure if I used it incorrectly, but I got what I needed!

Thanks a lot.

 

[markros]

Wow, in my query I just noticed I had a mistake - it should have been Products.ProductID = X.MaxProduct at the end.

In any case, if you have several records with MaxProductID this query would have returned all of them.

Did you look the Blog I gave you a link to? If you have SQL Server 2005 or up, then you can use easier ROW_NUMBER() solution which also would guarantee uniqueness. Or you can use TOP 1 subselect.

Please review the blog and try to apply solutions listed there to your problem by yourself. I would also appreciate if you can test all of them and post your performance results.

Thanks in advance.

 

[zyman10]

Yeah I looked at the blog, but some of them won't work for me since I am joining.

I will go ahead and try all of these and even the ROW_NUMBER() because I am SQL Express 2008. The only way I know how to post performance results is by listing the time the query took. Is that what you mean? Or is there some built in benchmarking I could use?

 

[markros]

Yes, post the times using SET STATISTICS TIME ON/OFF before/after each query. I think it would be enough for now. You can only view the actual execution time and see time relative to the batch in %.