Hi! I used below query, but couldn’t got requirement. I need get value where it has max id from @Detail.
DECLARE @Person AS TABLE(Id INT,Name VARCHAR(10))
INSERT INTO @Person VALUES(1,'Rustam')
INSERT INTO @Person VALUES(2,'Asror')
DECLARE @mainTable AS TABLE(Id INT,PersonId int,Price INT, Code VARCHAR(2))
INSERT INTO @mainTable VALUES(1,1,10, 'AA')
INSERT INTO @mainTable VALUES(2,1,15, 'BB')
INSERT INTO @mainTable VALUES(3,1,4, 'CC')
DECLARE @Detail AS TABLE(Id INT, PersonId int,Price INT, Code VARCHAR(2))
INSERT INTO @Detail VALUES(1,1,9, 'AA')
INSERT INTO @Detail VALUES(2,1,8, 'AA')
INSERT INTO @Detail VALUES(3,1,7, 'AA')
INSERT INTO @Detail VALUES(4,1,6, 'AA')
INSERT INTO @Detail VALUES(6,1,15, 'BB')
INSERT INTO @Detail VALUES(7,1,14, 'BB')
INSERT INTO @Detail VALUES(8,1,13, 'BB')
INSERT INTO @Detail VALUES(9,1,4, 'CC')
INSERT INTO @Detail VALUES(10,1,5, 'CC')
select p.Name, m.Price, m.Code, min(d.Price) as Rem from @mainTable m inner join @Detail d on m.Code = d.Code join @Person p on p.Id = m.PersonId where m.PersonId = 1 and d.Id = (select max(Id) from @Detail where PersonId = 1) group by p.Name, m.Price, m.Code
I want this result:
|
Name
|
Price
|
Code
|
Rem
|
|
Rustam
|
4
|
CC
|
5
|
|
Rustam
|
10
|
AA
|
6
|
|
Rustam
|
15
|
BB
|
13
|
