-1

I want to GROUP rows together based on a field, then SELECT 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:

3A-CSTC,00246-01
3A-CSTC,00246-02
3A-CSTC,00246-03

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

Data Definitions

TABLE_NAME     COLUMN_NAME  PRIMARY_KEY   DATA_TYPE   IS_NULLABLE
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.

Community
  • 1
  • 1
KRob
  • 389
  • 3
  • 19
  • Show us the table definition. You state that it is a compound key, yet the code shows it as 1 column. And which DBMS are you using? – ypercubeᵀᴹ May 05 '14 at 21:40
  • If I was using compound key correctly- model, make, region, and iteration are 4 separate columns defined in a different table. In these Product tables, they are combined into one column. It is a RDBMS, using Microsoft SQL Server. – KRob May 05 '14 at 22:01
  • 1
    I did not say you are doing anything wrong. But again: Show us the tables' definitions. We can't understand what you mean with "combined". – ypercubeᵀᴹ May 05 '14 at 22:02
  • added definition in edit – KRob May 05 '14 at 22:42

2 Answers2

1

You should probably GROUP BY a substring of the ProductID and SELECT using an aggregate function:

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

This is assuming that the ProductID remains a consistent length and you can count on the iterations not going over two digits.

user1261365
  • 78
  • 4
  • Yes sir, you are correct. I am using the LEFT() to GROUP BY, but I left that out in my example code. Do you know how I could return only 1 row from that group? – KRob May 05 '14 at 22:06
  • Not valid sql, but for an example: GROUP BY LEFT(pt1.ProductID,13) WHERE pt1.Iteration is the greatest number of this group. – KRob May 05 '14 at 22:07
0

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
mgmedick
  • 686
  • 7
  • 23
  • I want to return 1 row from ProductsTable1. Hopefully the data definition that I added clears up your second questions. Well I guess you answered my question if you cannot select a single row from the DB when grouping. Thank you for the additional recommendation. – KRob May 05 '14 at 22:46
  • 1
    yea when your grouping, you get an error saying the field is not in an aggregate function...so something like: select blah, blah1 from table groupby blah will not work because blah1 can't just be selected. The best way to get around this is to do the grouping in a subselect join and make sure you select whatever specific id that makes each row unique. Then join that back to the original table using that id, and you can then select the columns you want. – mgmedick May 07 '14 at 22:30