October 6, 2012 at 2:34 pm
I Have Two Table Arazi and AraziEghdamat and the Relationship between them is 1(Arazi) to Many(AraziEghdamat).
i want to select all Data from Arazi And Select The last date of AraziEghdamat table by column EghdamatSabt(Datetime Type) according to Arazi.how can i Warite the Query????
--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
October 6, 2012 at 4:15 pm
Try:
select a.*, max(e.EghdamatSabt) as EghdamatSabt
from Arazi as a
join AraziEghdamat as e on e.AraziID = a.AraziID
group by a.*
Hope this helps.
October 6, 2012 at 11:11 pm
i want alo info of last date of AraziEghdamat and when I want To select them it give this error:
Msg 8120, Level 16, State 1, Line 1
Column 'AraziEghdamat.Eghdamat' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
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
when i use them in group by it gives all of AraziEghdamat:
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
October 6, 2012 at 11:37 pm
Try:
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
Hope this helps.
October 8, 2012 at 2:06 pm
Another option (scripts to create tables, indexes, and sample data attached):
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply