Why does this need an aggregate?
SELECT TOP 1 m.Title, s.Cost
FROM Main m INNER JOIN Stock S ON
s.Product_id = m.ProductID
WHERE 1=1 (your condition)
ORDER BY s.UpdateDate DESC
On Nov 19, 2007 7:12 AM, James Smith <[EMAIL PROTECTED]> wrote:
> I am trying to select the most recent cost price for a product, the simple
> solution would seem to be...
>
> SELECT m.Title, LAST(s.cost) AS Cost
> FROM main m
> JOIN stock s ON (s.ProductID = m.ProductID)
> WHERE some conditions
>
> However the LAST aggregate function seems not to be supported in MSSQL 2000
> and I don't fancy and upgrade to 2005 just to get this one function.
>
> Any better ideas anyone?
>
> --
> Jay
>
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.5.503 / Virus Database: 269.16.0/1137 - Release Date: 18/11/2007
> 17:15
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Enterprise web applications, build robust, secure
scalable apps today - Try it now ColdFusion Today
ColdFusion 8 beta - Build next generation apps
Archive:
http://www.houseoffusion.com/groups/CF-Community/message.cfm/messageid:246696
Subscription: http://www.houseoffusion.com/groups/CF-Community/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.5
