Change Query from the TOP clause 1

[schwarem]

I am creating a Smart Client application using the SQL Server Compact Edition. The compact edition does not support the top clause. I need to recreated the functionality of the query listed below without using the top clause, because it is not supported in CE. The query below returns the Address information for the most recent Address of customers attending a meeting. The customer can have multiple current addresses, so the most recent one is the one with the latest DateCreated. I'm guessing I can do this with the MAX aggregate function, but I haven't been able to figure it out.

SELECT     Customer.CustID, Customer.Prefix, Customer.FName, Customer.MName, Customer.LName, Customer.Suffix, Organizations.OrgName, 
                      a.Com, a.Email, CustMeetingsID
FROM         Addresses AS a INNER JOIN
                      Customer ON a.CustID = Customer.CustID INNER JOIN
                      Organizations ON a.OrgID = Organizations.OrgID INNER JOIN
                      CustMeetings ON Customer.CustID = CustMeetings.CustID
WHERE     (a.AddressID IN
                          (SELECT     TOP (1) AddressID
                            FROM          Addresses AS b
                            WHERE      (a.CustID = CustID) AND (CurrentAddress = 1)
                            ORDER BY DateCreated DESC)) AND (CustMeetings.MeetingID = @MeetingID)
ORDER BY Customer.LName, Customer.FName, Customer.MName, Organizations.OrgName

 

[gmmastros]

Try removing the parenthesis from the 1.

[COLOR=blue]SELECT[/color]  Customer.CustID, 
		Customer.Prefix, 
		Customer.FName, 
		Customer.MName, 
		Customer.LName, 
		Customer.Suffix, 
		Organizations.OrgName, 
        a.Com, 
		a.Email, 
		CustMeetingsID
[COLOR=blue]FROM[/color]    Addresses [COLOR=blue]AS[/color] a [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color]
		Customer [COLOR=blue]ON[/color] a.CustID = Customer.CustID [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color]
		Organizations [COLOR=blue]ON[/color] a.OrgID = Organizations.OrgID [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color]
		CustMeetings [COLOR=blue]ON[/color] Customer.CustID = CustMeetings.CustID
[COLOR=blue]WHERE[/color]     (a.AddressID IN
          ([COLOR=blue]SELECT[/color]     TOP 1 AddressID
            [COLOR=blue]FROM[/color]          Addresses [COLOR=blue]AS[/color] b
            [COLOR=blue]WHERE[/color]      (a.CustID = CustID) AND (CurrentAddress = 1)
            [COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] DateCreated [COLOR=#FF00FF]DESC[/color])) AND (CustMeetings.MeetingID = @MeetingID)
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] Customer.LName, Customer.FName, Customer.MName, Organizations.OrgName

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[schwarem]

The query works fine in SQL Server 2005. I am trying to use it in SQL Server 2005 Compact Edition, which does not support the TOP function. Getting rid of the parenthesis still results in an error in the Compact Edition.

 

[RyanEK]

If your Datecreated field is unique, this should work.

SELECT      Customer.CustID, 
			Customer.Prefix, 
			Customer.FName, 
			Customer.MName, 
			Customer.LName, 
			Customer.Suffix, 
			Organizations.OrgName, 
            a.Com, 
			a.Email, 
			CustMeetingsID
FROM	Addresses AS a INNER JOIN
		Customer ON a.CustID = Customer.CustID INNER JOIN
        Organizations ON a.OrgID = Organizations.OrgID INNER JOIN
        CustMeetings ON Customer.CustID = CustMeetings.CustID
WHERE   (a.AddressID IN (SELECT AddressID
			FROM  Addresses AS b
            WHERE (a.CustID = CustID) AND (CurrentAddress = 1)
			AND DateCreated = (select max(DateCreated) from Addresses as c 
				where c.CustID = b.CustID  AND (CurrentAddress = 1))) 
		AND (CustMeetings.MeetingID = @MeetingID)
ORDER BY Customer.LName, Customer.FName, Customer.MName, Organizations.OrgName

 

[schwarem]

Thanks Ryan. That worked.