Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error With Query


Error With Query

Author
Message
vahid.arr
vahid.arr
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 180
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




imex
imex
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 199
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.

http://www.imoveisemexposicao.com.br
vahid.arr
vahid.arr
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 180
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


imex
imex
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 199
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.

http://www.imoveisemexposicao.com.br
Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 1721
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



Attachments
MaxDateExample.txt (0 views, 10.00 MB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search