KRob KRob -5 years ago 100
SQL Question

How to SELECT only one record from a group of related records in SQL?

I want to

rows together based on a field, then
only 1 of those fields from each group.

Say I have a ProductID which is a
compound key
key based on 4 fields {model, make, region, iteration}. The ProductID is always in the format 00-0000-00000-00, and the iteration is a sequentially incremented integer. I can group all of the products together where the model, make and region are the same using the below SQL:

SELECT pt1.ProductID
FROM ProductsTable pt1
INNER JOIN ProductsTable2 pt2 ON pt1.ProductID = pt2.ProductID
GROUP BY LEFT(pt1.ProductID, 13)

which would have one GROUP something like:


How would I return only the row that has the highest iteration in the ProductID (in this case 3A-CSTC,00246-03)?

Data Definitions

Models ModelID TRUE char(2) NO
Makes MakeID TRUE char(4) NO
Regions RegionID TRUE char(5) NO
Iterations IterationId TRUE char(2) NO
ProductTable1 ProductID TRUE char(16) NO
ProductTable2 ProductID TRUE char(16) NO

So the ProductId = ModelID + "-" + MakeID + "-" + RegionID + "IterationId"

I am aware of the HAVING clause; however, I have no idea on what expression I could use.
I did come across a similar, but different question.

Answer Source

I don't fully comprehend that data scenario your dealing with, when you say you want to return one row, from what table do you want the row from, and what table is causing the multiple records? selecting a simple row from the db is not possible when you are grouping. You would have to aggregate everything you are trying to select. It sounds like you need to leverage your primary key on one of the tables and do a sub-select of some type.

select * from ProductsTable pt1
join (select left(pt1.ProductID, 13) as prodid, max(pt2.primarykey) as primarykey from ProductsTable pt2 group by left(pt1.ProductID, 13)) as a on a.primarykey=pt1.primarykey
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download