Max in select clause

I have the following code......
SELECT
a..acct_nbr
,p.pric_uval_amt AS 'Unit Price'
,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
FROM
account_t a
LEFT OUTER JOIN price_t p
ON a.ser_id = p.ser_id
GROUP BY
a.acct_nbr ,p.pric_uval_amt
Its purpose is to return an account number from one table, and from the
corresponding price table the latest unit price date and unit price.
Basically the price table
keeps a list of all products associated with an account. Each account can
have multiple products. The price table keeps regularly updated prices for
these products and whatever the date that theprice was updated. What I want
to do is to be able to get the latest (max) unit price date and the
corresponding unit price value.
The code above gives me the latest unit price date (if I take out the
,p.pric_uval_amt AS 'Unit Price' line). If I leave that line in I also get
the prices at all previous dates.
What I tend to get is this......
acct_nbr unit price
unit_price_date
----
0001 90
05/05/2005
0001 98
05/06/2005
0001 91
05/07/2005
0002 43
05/05/2005
0002 45
05/06/2005
When what I want is this.........
acct_nbr unit price
unit_price_date
----
0001 91
05/07/2005
0002 45
05/06/2005
Any idea's ?Hi
Is this the query that you are looking for:
SELECT account_t.acct_nbr, account_t.[unit price], derived.Unit_Price_Date
FROM account_t
INNER JOIN (
SELECT a.acct_nbr,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
FROM account_t a
LEFT OUTER JOIN price_t p ON a.ser_id = p.ser_id
GROUP BY a.acct_nbr
) derived
ON derived.Unit_Price_Date = account_t.Unit_Price_Date AND
derived.acct_nbr = account_t.acct_nbr
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"quiglepops" wrote:

> I have the following code......
>
> SELECT
> a..acct_nbr
> ,p.pric_uval_amt AS 'Unit Price'
> ,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
> FROM
> account_t a
> LEFT OUTER JOIN price_t p
> ON a.ser_id = p.ser_id
> GROUP BY
> a.acct_nbr ,p.pric_uval_amt
> Its purpose is to return an account number from one table, and from the
> corresponding price table the latest unit price date and unit price.
> Basically the price table
> keeps a list of all products associated with an account. Each account can
> have multiple products. The price table keeps regularly updated prices for
> these products and whatever the date that theprice was updated. What I wan
t
> to do is to be able to get the latest (max) unit price date and the
> corresponding unit price value.
> The code above gives me the latest unit price date (if I take out the
> ,p.pric_uval_amt AS 'Unit Price' line). If I leave that line in I also get
> the prices at all previous dates.
> What I tend to get is this......
> acct_nbr unit price
> unit_price_date
> ----
> 0001 90
> 05/05/2005
> 0001 98
> 05/06/2005
> 0001 91
> 05/07/2005
> 0002 43
> 05/05/2005
> 0002 45
> 05/06/2005
>
> When what I want is this.........
> acct_nbr unit price
> unit_price_date
> ----
> 0001 91
> 05/07/2005
> 0002 45
> 05/06/2005
>
> Any idea's ?
>
>|||Try,
Try,
SELECT
a..acct_nbr
,p.pric_uval_amt AS 'Unit Price'
,p.pric_asof_dt AS 'Unit_Price_Date'
FROM
account_t as a
LEFT OUTER JOIN
price_t as p
ON a.ser_id = p.ser_id
where
p.pric_asof_dt = (select max(p1.pric_asof_dt) from price_t as p1 where
p1.ser_id = a.ser_id)
AMB
"quiglepops" wrote:

> I have the following code......
>
> SELECT
> a..acct_nbr
> ,p.pric_uval_amt AS 'Unit Price'
> ,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
> FROM
> account_t a
> LEFT OUTER JOIN price_t p
> ON a.ser_id = p.ser_id
> GROUP BY
> a.acct_nbr ,p.pric_uval_amt
> Its purpose is to return an account number from one table, and from the
> corresponding price table the latest unit price date and unit price.
> Basically the price table
> keeps a list of all products associated with an account. Each account can
> have multiple products. The price table keeps regularly updated prices for
> these products and whatever the date that theprice was updated. What I wan
t
> to do is to be able to get the latest (max) unit price date and the
> corresponding unit price value.
> The code above gives me the latest unit price date (if I take out the
> ,p.pric_uval_amt AS 'Unit Price' line). If I leave that line in I also get
> the prices at all previous dates.
> What I tend to get is this......
> acct_nbr unit price
> unit_price_date
> ----
> 0001 90
> 05/05/2005
> 0001 98
> 05/06/2005
> 0001 91
> 05/07/2005
> 0002 43
> 05/05/2005
> 0002 45
> 05/06/2005
>
> When what I want is this.........
> acct_nbr unit price
> unit_price_date
> ----
> 0001 91
> 05/07/2005
> 0002 45
> 05/06/2005
>
> Any idea's ?
>
>|||Thanks everyone for helping.
Alejandro, used your solution worked well. Thanks a lot.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B65F1A34-7B16-46BB-B371-6143E989ACC5@.microsoft.com...
> Try,
> Try,
> SELECT
> a..acct_nbr
> ,p.pric_uval_amt AS 'Unit Price'
> ,p.pric_asof_dt AS 'Unit_Price_Date'
> FROM
> account_t as a
> LEFT OUTER JOIN
> price_t as p
> ON a.ser_id = p.ser_id
> where
> p.pric_asof_dt = (select max(p1.pric_asof_dt) from price_t as p1 where
> p1.ser_id = a.ser_id)
>
> AMB
>
> "quiglepops" wrote:
>
can
for
want
get