September 1, 2009 at 2:52 pm
This article should be helpful
Limit Groups by Number Using Transact-SQL
September 1, 2009 at 8:40 pm
Assuming you mean to get the latest pricing information for each asset based
on the latest primary key in Pricing, then it will look like this:
-- SQL Server 2000
SELECT A.asset_name,
P.phdate,
P.price
FROM Asset AS A
LEFT OUTER JOIN
(SELECT assetid, phdate, price
FROM Pricing AS P1
WHERE prid = (SELECT MAX(prid)
FROM Pricing AS P2
WHERE P2.assetid = P1.assetid)) AS P
ON A.assetid = P.assetid
-- SQL Server 2005
SELECT A.asset_name,
P.phdate,
P.price
FROM Asset AS A
LEFT OUTER JOIN
(SELECT assetid, phdate, price,
ROW_NUMBER() OVER(
PARTITION BY assetid
ORDER BY prid DESC) AS seq
FROM Pricing) AS P
ON A.assetid = P.assetid
AND P.seq = 1;
-- SQL Server 2005
SELECT A.asset_name,
P.phdate,
P.price
FROM Asset AS A
OUTER APPLY
(SELECT TOP(1) assetid, phdate, price
FROM Pricing AS P1
WHERE A.assetid = P1.assetid
ORDER BY prid DESC) AS P;
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply