--Arazi
CREATE TABLE [dbo].[Arazi](
	[AraziID] [int] IDENTITY(100,1) NOT NULL,
	[MantagheID] [smallint] NOT NULL,
	[EndUserID] [nvarchar](15) NULL,
	[MahaleTakhrib] [nvarchar](250) NULL,
	[MasahatArazi] [decimal](12, 6) NOT NULL,
	[AraziImage] [varbinary](max) NULL,
	[AraziMokhtasat] [varchar](4000) NULL,
	[AraziSabt] [datetime] NOT NULL,
	[AraziEdit] [datetime] NULL,
 CONSTRAINT [PK_Arazi] PRIMARY KEY CLUSTERED 
(
	[AraziID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
--AraziEghdamat
CREATE TABLE [dbo].[AraziEghdamat](
	[A_EghdamatID] [int] IDENTITY(1,1) NOT NULL,
	[AraziID] [int] NOT NULL,
	[EndUserID] [nvarchar](15) NULL,
	[Eghdamat] [nvarchar](500) NOT NULL,
	[RafeTasarof] [bit] NOT NULL,
	[MasahatAraziKhaleShode] [decimal](12, 6) NULL,
	[EghdamatSabt] [datetime] NOT NULL,
	[EghdamatEdit] [datetime] NULL,
 CONSTRAINT [PK_ArazIEghdamat] PRIMARY KEY CLUSTERED 
(
	[A_EghdamatID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AraziEghdamat]  WITH CHECK ADD  CONSTRAINT [FK_ArazIEghdamat_Arazi] FOREIGN KEY([AraziID])
REFERENCES [dbo].[Arazi] ([AraziID])
GO

ALTER TABLE [dbo].[AraziEghdamat] CHECK CONSTRAINT [FK_ArazIEghdamat_Arazi]
GO

select a.*, max(e.EghdamatSabt) as EghdamatSabt
from Arazi as a
join AraziEghdamat as e on e.AraziID = a.AraziID
group by a.*

select a.*, max(e.EghdamatSabt) as EghdamatSabt,e.Eghdamat,e.EghdamatSabt,e.EndUserID,e.MasahatAraziKhaleShode,e.RafeTasarof
from Arazi as a
join AraziEghdamat as e on e.AraziID = a.AraziID
group by a.AraziEdit,a.AraziID,a.AraziImage,a.AraziMokhtasat,a.AraziSabt,a.EndUserID,a.MahaleTakhrib,a.MantagheID,a.MasahatArazi

select a.*, max(e.EghdamatSabt) as EghdamatSabt,e.Eghdamat,e.EghdamatSabt,e.EndUserID,e.MasahatAraziKhaleShode,e.RafeTasarof
from Arazi as a
join AraziEghdamat as e on e.AraziID = a.AraziID
group by a.AraziEdit,a.AraziID,a.AraziImage,a.AraziMokhtasat,a.AraziSabt,a.EndUserID,a.MahaleTakhrib,a.MantagheID,a.MasahatArazi
,EghdamatSabt,e.Eghdamat,e.EghdamatSabt,e.EndUserID,e.MasahatAraziKhaleShode,e.RafeTasarof

with CTE as
(
    select *, ROW_NUMBER() OVER(PARTITION BY AraziID 
                                ORDER BY EghdamatSabt DESC) AS RowNum
    from AraziEghdamat
)

select a.*, e.*
from Arazi as a
join CTE as e on e.AraziID = a.AraziID and e.RowNum = 1

SELECT
    a.AraziID
   ,a.MantagheID
   ,a.EndUserID
   ,a.MahaleTakhrib
   ,a.MasahatArazi
   ,a.AraziImage
   ,a.AraziMokhtasat
   ,a.AraziSabt
   ,a.AraziEdit
   ,ae.Eghdamat
   ,ae.RafeTasarof
   ,ae.MasahatAraziKhaleShode
   ,ae.EghdamatSabt
   ,ae.EghdamatEdit
FROM
    dbo.Arazi AS a
INNER JOIN 
    dbo.AraziEghdamat AS ae
    ON a.AraziID = ae.AraziID
        AND a.EndUserID = ae.EndUserID
INNER JOIN     
    (
    SELECT DISTINCT
        a1.AraziID
       ,ae1.EndUserID
       ,(
         SELECT
            MAX(EghdamatSabt)
         FROM
            dbo.AraziEghdamat
         WHERE
            AraziID = a1.AraziID
            AND EndUserID = a1.EndUserID
        ) AS MaxDate
    FROM
        dbo.Arazi AS a1
    INNER JOIN dbo.AraziEghdamat AS ae1
        ON a1.AraziID = ae1.AraziID
           AND a1.EndUserID = ae1.EndUserID
    ) AS Sub
    ON ae.AraziID = Sub.AraziID
    AND ae.EndUserID = Sub.EndUserID
    AND ae.EghdamatSabt = Sub.MaxDate
ORDER BY
    a.AraziID