Hello All,

I have a parent table ASSET:

CREATE TABLE [dbo].[ASSET](
[ASSETID] [int] IDENTITY(1,1) NOT NULL,
[ASSET_NAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]

with the following data:

INSERT INTO ASSET (ASSET_NAME) VALUES ('COMPANY1')
INSERT INTO ASSET (ASSET_NAME) VALUES ('COMPANY2')
INSERT INTO ASSET (ASSET_NAME) VALUES ('COMPANY3')

I also have a child table PRICING:

CREATE TABLE [dbo].[PRICING](
[PRID] [int] IDENTITY(1,1) NOT NULL,
[PHDATE] [datetime] NOT NULL CONSTRAINT [DF_PRICING_PHDATE] DEFAULT
(getdate()),
[PRICE] [float] NOT NULL,
[ASSETID] [int] NOT NULL,
CONSTRAINT [PK_PRICING] PRIMARY KEY CLUSTERED
(
[PRID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

with the following data:

INSERT INTO PRICING (PRICE, ASSETID) VALUES (100, 1)
INSERT INTO PRICING (PRICE, ASSETID) VALUES (150, 1)
INSERT INTO PRICING (PRICE, ASSETID) VALUES (175, 1)

I am trying to create the following output:

ASSET_NAME PHDATE PRICE
--------------------- ------------ ---------
COMPANY 1 timestamp 175
COMPANY 2 null null
COMPANY 3 null null

i.e. only return a single row from the PRICING table when joining the
ASSET table to the PRICING table.

Pointers appreciated
Thankyou!

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;


HTH,

Plamen Ratchev
http://www.SQLStudio.com

On Feb 17, 7:30*pm, "Plamen Ratchev" <Pla... (AT) SQLStudio (DOT) com> wrote:
all set.
thankyou plamen!