Sql-server – How to use MAX() to select a single record but prevent GROUP BY from selecting all distinct values and multiple records

greatest-n-per-groupsql servert-sql

I have some Line items that have version numbers:

[Doc No]    [Version No]    [Line No]    [Data-a]    [Data-b]
1           1               1000         abc         abc
1           2               1000         xyz         abc
1           1               2000         null        lmnop
1           2               2000         ggg         lmnop

I want to only select the highest version number per [Line No]:

[Doc No]    [Version No]    [Line No]    [Data-a]    [Data-b]
1           2               1000         xyz         abc
1           2               2000         ggg        lmnop

I am doing this using this tsql:

SELECT [Doc No]
      ,max([Version No])
      ,[Line No]
      ,[Data-a]
      ,[Data-b]
FROM [table]
GROUP BY [Doc No], [Line No], [Data-a], [Data-b]

The problem obviously is … if you notice in the first table the [Data-a] column has changing values (hence the need for version numbers. So I want to see the latest version and the value contained in [Data-a] for that record. But since I am using max() to get the latest version, I have to put [Data-a] in the GROUP BY clause – which causes each two distinct values to be pulled and thus two records showing both version numbers. How can I solve this problem?

Best Answer

You can accomplish this with a subquery that finds the maximum Version No by Doc No and Line No.

CREATE TABLE #temp ([Doc No]  INT, [Version No] INT, [Line No]  INT, [Data-a] VARCHAR(5), [Data-b] VARCHAR(5));

INSERT INTO #temp VALUES (1,1,1000,'abc','abc')
INSERT INTO #temp VALUES(1,2,1000,'xyz','abc')
INSERT INTO #temp VALUES(1,1,2000,null,'lmnop')
INSERT INTO #temp VALUES(1,2,2000,'ggg','lmnop')

SELECT a.*
FROM #temp as a
JOIN
    ( 
SELECT [Doc No] 
        ,[Line No] 
      ,max([Version No]) as MaxVersion
FROM #temp
GROUP BY [Doc No] , [Line No]
) as b on a.[Doc No]  = b.[Doc No]  and a.[Line No] = b.[Line No] and a.[Version No] = b.MaxVersion

DROP TABLE #temp
Related Question