4

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?

0

4 Answers 4

3

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
3
  • Your CREATE TABLE #temp and INSERT statements are making an example table, right? They are not part of the query?
    – KAE
    Apr 8, 2021 at 15:21
  • 1
    @KAE, yes that is correct. The CREATE and INSERT are only used here to have sample data.
    – tpet
    Apr 8, 2021 at 15:38
  • If the goal was to find the maximum Version No by Line No only (not Doc No), would your second SELECT clause change to, SELECT a.* FROM #temp as a JOIN ( SELECT [Line No] ,max([Version No]) as MaxVersion FROM #temp GROUP BY [Line No] ) as b on a.[Line No] = b.[Line No] and a.[Version No] = b.MaxVersion
    – KAE
    Apr 8, 2021 at 17:42
4

Just incase you need another solution. Below uses row_number() over (partition by .. order by)

CREATE TABLE dbo.docStore
    ([Doc No] int, 
     [Version No] int,
     [Line No] int ,
     [Data-a] varchar(10),
     [Data-b] varchar(10))
;

INSERT INTO dbo.docStore
    ([Doc No],[Version No],[Line No],[Data-a],[Data-b])
VALUES
    (1,1,1000,'abc','abc'    ),
    (1,2,1000,'xyz','abc'    ),
    (1,1,2000,null,'lmnop'   ),
    (1,2,2000,'ggg','lmnop'  )
;

with cte as 
(
select [Doc No], [Version No], [Line No], [Data-a], [Data-b], 
        row_number() over (partition by [Doc No], [Line No] 
                           order by [Version No] desc) 
        as rn 
from dbo.docStore
)
select [Doc No], [Version No], [Line No], [Data-a], [Data-b] 
from cte
where rn = 1
;

-- clean up 
drop table dbo.docStore 
;
0
1

This get you what you need?

;with cteMaxVers ([Line No], [Version No] 
AS (SELECT [Line No], MAX([Version No])
FROM [table]
GROUP BY [Line No]  
)  
SELECT t.[Doc No]
      ,t.[Version No]
      ,t.[Line No]
      ,t.[Data-a]
      ,t.[Data-b]
FROM [table] t 
JOIN cteMaxVers c ON t.[Line No] = c.[Line No] AND t.[Version No] = c.[Version No];
0

Borrowing @Kin's code but adding one more record to show the correct answer.

use tempdb

CREATE TABLE dbo.docStore
    ([Doc No] int, [Version No] int,[Line No] int ,[Data-a] varchar(10),[Data-b] varchar(10))
;

INSERT INTO dbo.docStore
    ([Doc No],[Version No],[Line No],[Data-a],[Data-b])
VALUES
    (1,1,1000,'abc','abc'    ),
    (1,2,1000,'xyz','abc'    ),
    (1,1,2000,null,'lmnop'   ),
    (1,2,2000,'ggg','lmnop'  ),
    (1,3,2000,'ddd','test')
;

with cte as 
(
select [Doc No],[Version No],[Line No],[Data-a],[Data-b], row_number() over (partition by [Doc No],[Line No] order by [Version No] desc) as rn from dbo.docStore
)
select [Doc No],[Version No],[Line No],[Data-a],[Data-b] from cte
where rn =1


-- clean up 
drop table dbo.docStore

This will ensure that we will get the largest [Version No] per group of [Doc No] and [Line No]

The result is as follows: enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.