Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error With Query Expand / Collapse
Author
Message
Posted Saturday, October 6, 2012 2:34 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 6, 2014 6:12 AM
Points: 53, Visits: 165
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



Post #1369464
Posted Saturday, October 6, 2012 4:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 12:46 PM
Points: 44, Visits: 171
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
Post #1369473
Posted Saturday, October 6, 2012 11:11 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 6, 2014 6:12 AM
Points: 53, Visits: 165
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

Post #1369497
Posted Saturday, October 6, 2012 11:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 12:46 PM
Points: 44, Visits: 171
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
Post #1369499
Posted Monday, October 8, 2012 2:06 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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




  Post Attachments 
MaxDateExample.txt (0 views, 10.80 MB)
Post #1370031
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse