Oh, yeah...sorry...I had started to approach it a slightly different way and then changed my mind. Forgot to go back to the GROUP BY to change it to match.

I don't think you can do this with MS-SQL, this can be done with Oracle which supports better use of aggregation and sub queries.

When using MS-Sql I put the data into a temp table and add some fields, one of the fields being the right 3 characters, then in another field I initially store blanks or null. Then I go back and update the temp table setting the last field to "dupe" or whatever you want to return. Then I select the first and the last field to get what you want. But its done via a temp table, not just a straight query. If you need an example of how the code works, request it.

Ummm...if you can't do it with SQL Server, then I guess he must have been imagining things when he said "Excellent!"

Yes, you can. Yes, it worked.

As he said, the final WORKING form was

SELECT data.part
FROM data,
     ( SELECT LEFT(part,5) AS prefix, MIN(SUBSTRING(part,6,1) AS subtype, RIGHT(part,3) AS series
       FROM data
       GROUP BY LEFT(part,5), RIGHT(part,3) ) AS check
WHERE LEFT(data.part,5) = check.prefix AND RIGHT(data.part,3) = check.series
ORDER BY check.prefix, check.subtype, check.series, data.part

Only mistake I made was I'd left a + sign in where the red comma now is.

Real glad I'm not the only one who needs a trip to the eye doctor... ;)

--Jeff Moden