But I am selecting multiple products at a time, your solution allows for
only one product.
-Original Message-
From: C. Hatton Humphrey [mailto:[EMAIL PROTECTED]
Sent: 19 November 2007 16:48
To: CF-Community
Subject: Re: MSSQL Aggregate Functions
Hence the reason for the TOP 1 modifier
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
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
You don't have a date stamp or an enumerated primary key to help you
determine which cost is the most recent You REALLY need a better way of
determining order than position in the database...ie last.
On Nov 19, 2007 6:12 AM, James Smith [EMAIL PROTECTED] wrote:
I am trying to select the
I do, I have both of those and currently do a join onto a subquery to get
this data but the LAST() function would make for much simpler code.
--
Jay
-Original Message-
From: G Money [mailto:[EMAIL PROTECTED]
Sent: 19 November 2007 13:34
To: CF-Community
Subject: Re: MSSQL Aggregate
'cause each record in the main table can have many in the stock table and I
only want one result with the most recent cost.
-Original Message-
From: C. Hatton Humphrey [mailto:[EMAIL PROTECTED]
Sent: 19 November 2007 12:31
To: CF-Community
Subject: Re: MSSQL Aggregate Functions
Why does
But ultimately unpredictable, and therefore not always correct. You don't
want the last record inserted, you want the most recent cost price. Maybe
they should always be the same, but they might not for whatever reason.
You need to join to your table that has either your aggregator or your date
if there
is a SQL Server 2000 equivalent to the LAST() function from SQL Server
2005.
-Original Message-
From: G Money [mailto:[EMAIL PROTECTED]
Sent: 19 November 2007 15:36
To: CF-Community
Subject: Re: MSSQL Aggregate Functions
But ultimately unpredictable, and therefore not always correct. You
Humphrey [mailto:[EMAIL PROTECTED]
Sent: 19 November 2007 12:31
To: CF-Community
Subject: Re: MSSQL Aggregate Functions
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
