trouble dealing with max() function in sql server-CodePudding

the whole code is part of a stored procedure. i have two pieces of code->

SELECT *
    FROM eng
    FULL JOIN ent ON eng.EntId = ent.EntId
    FULL JOIN del ON del.EngId = eng.EngId
    FULL JOIN cli ON cli.CId = ent.CId
    WHERE  eng.[Version] = (SELECT MAX([Version]) FROM eng b WHERE eng.EngId=b.EngId AND EntId =@EntId group by EngId)
    AND ent.[Version] = (SELECT MAX([Version]) FROM ent e WHERE ent.EntId=e.EntId AND EntId =@EntId group by EntId)
    AND ent.EntId =@EntityId

this one works fine. in the line-> eng.[Version] = (SELECT MAX([Version]) FROM eng b ...

this particular piece is producing the output->

No Column name
1
2

and the other one->

SELECT * FROM [dbo].[del] D WITH(NOLOCK)
              FULL JOIN eng EG WITH(NOLOCK) ON EG.EngId=D.EngId
              FULL JOIN [dbo].[ent] E WITH(NOLOCK) ON E.EntId=D.EntId
              FULL JOIN [dbo].cli cli ON cli.CliId = E.CliId
              WHERE D.[Version] = (SELECT MAX([Version]) FROM del d WHERE D.DelId=d.DelId AND EngId = @EngId group by DelId)
                AND EG.[Version] = (SELECT MAX([Version]) FROM eng eg WHERE EG.EngId=eg.EngId AND EngId = @EngId group by EngId)
                AND E.[Version] = (SELECT MAX([Version]) FROM ent e WHERE E.EntId=e.EntId AND EntId = @EntId group by EntId)
                AND D.EngId=@EngId

this is not working correctly. it gives error-> Subquery returned more than 1 value. in this line-> D.[Version] = (SELECT MAX([Version]) FROM del d...

it independently gives output->

No column name
2
1

column and table names are dummy. can someone help please?

*******edit Sample data

Eng table

EngID    EntID     Entname   Version
Mana1    333113    name1        1
Mana1    333113    name2        2



Del table

DelID     EntID     EngID    Delname      Version
D110      333113    Mana1    delname1        1
D110      333113    Mana1    delname2        2
D111      333113    Mana1    delnewname      1

i need to pull data from these 2 tables. output i need->

EntID     EngID    DelID    Entname   Delname    Version(from del table)

333113    Mana1    D110     name2     delname2    2
333113    Mana1    D111     name2     delnewname  1

logic is pull latest version from eng table and map them to rows in del table and find the latest version there as well.

CodePudding user response：

I'm not getting any issues. Pleaase provide table definitions and sample data.

create table eng(Version int, EntId int, EngId int);
create table ent(Version int, EntId int, EngId int, Cid int);
create table del(Version int, EngId int);
create table cli(Version int, CId int);
insert into eng values (1,1,1),(1,1,1);
insert into ent values (1,1,1,1),(1,1,1,1);
insert into del values(1,1),(1,1);
insert into cli values(1,1),(1,1);
GO

8 rows affected

DECLARE @EntityID int;
DECLARE @EntID int;
SET @EntityID = 1;
SET @EntID = 1;
SELECT *
    FROM eng
    FULL JOIN ent ON eng.EntId = ent.EntId
    FULL JOIN del ON del.EngId = eng.EngId
    FULL JOIN cli ON cli.CId = ent.CId
    WHERE  eng.[Version] = (SELECT MAX([Version]) FROM eng b WHERE eng.EngId=b.EngId AND EntId =@EntId group by EngId)
    AND ent.[Version] = (SELECT MAX([Version]) FROM ent e WHERE ent.EntId=e.EntId AND EntId =@EntId group by EntId)
    AND ent.EntId =@EntityId
GO

Version | EntId | EngId | Version | EntId | EngId | Cid | Version | EngId | Version | CId
------: | ----: | ----: | ------: | ----: | ----: | --: | ------: | ----: | ------: | --:
      1 |     1 |     1 |       1 |     1 |     1 |   1 |       1 |     1 |       1 |   1
      1 |     1 |     1 |       1 |     1 |     1 |   1 |       1 |     1 |       1 |   1
      1 |     1 |     1 |       1 |     1 |     1 |   1 |       1 |     1 |       1 |   1
      1 |     1 |     1 |       1 |     1 |     1 |   1 |       1 |     1 |       1 |   1
      1 |     1 |     1 |       1 |     1 |     1 |   1 |       1 |     1 |       1 |   1
      1 |     1 |     1 |       1 |     1 |     1 |   1 |       1 |     1 |       1 |   1
      1 |     1 |     1 |       1 |     1 |     1 |   1 |       1 |     1 |       1 |   1
      1 |     1 |     1 |       1 |     1 |     1 |   1 |       1 |     1 |       1 |   1
      1 |     1 |     1 |       1 |     1 |     1 |   1 |       1 |     1 |       1 |   1
      1 |     1 |     1 |       1 |     1 |     1 |   1 |       1 |     1 |       1 |   1
      1 |     1 |     1 |       1 |     1 |     1 |   1 |       1 |     1 |       1 |   1
      1 |     1 |     1 |       1 |     1 |     1 |   1 |       1 |     1 |       1 |   1
      1 |     1 |     1 |       1 |     1 |     1 |   1 |       1 |     1 |       1 |   1
      1 |     1 |     1 |       1 |     1 |     1 |   1 |       1 |     1 |       1 |   1
      1 |     1 |     1 |       1 |     1 |     1 |   1 |       1 |     1 |       1 |   1
      1 |     1 |     1 |       1 |     1 |     1 |   1 |       1 |     1 |       1 |   1

db<>fiddle here

CodePudding user response：

I think you have an typo in the following line

D.[Version] = (SELECT MAX([Version]) FROM del d WHERE D.DelId=d.DelId AND EngId = @EngId group by DelId)

where it reads WHERE D.DelId=d.DelId

You are comparing the same column from the same table,
so both queries are not doing the same thing.