Thank you for the clarification, I was aware that MAX is an aggregate; but you use it in such a way as to allow each row to be its own max and hence getting results for every row. I was excited yesterday because the longer I searched the farther away I got from the answer. It seems that it is hard for most SQL users to believe there are legitimate reasons for needing a long list of items with multiple instances to be made into a short list in SQL. Its almost as if no one else does ERP type reporting with quarterly figures that compare year over year figures for a variable and constantly changing group of people; which I know is not true.

One example, my own, is that I am producing a SSRS report in which I need to show a salespersons name and then the id next to it. However in Microsoft's Dynamics GP they allow sales people to have multiple ids; so "Jim Smith" can have ids "1234,1234z,1245x,1264" and if you need a list without the person being listed four times; you can have SSRS do the work or SQL. The rub comes in when you have 100+ sales people and each with a combination of between 1 and 9 ids and no control over whether or not to clean up the data or if the ids have a set pattern. Now your looking at just for two fields returning over 800+ rows and then having SSRS do the logic at runtime. The overall effect is a slow report that at best must be cached each night. Now say that management requires said report to be able to run ad-hoc during the production day with the latest information. Suddenly all the answers of SQL shouldn't be doing this or let SSRS handle it etc. sound academic and rather unhelpful. That is where your code comes in, now I can add it to my rather lengthy Stored Proc. and return just 100 records with name and id(s) at the beginning followed by other un-related data and the report runs quickly and quietly with no matrix pivot; sub-report; mish-mash for the reporting engine to translate before producing results.

In the future, I will be searching here first; for whatever reason sql server central does not get very good results in google or duckduckgo for specifics only on the generalities.