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

Matching relational records. Is it possible using Data Minig?

Problem:
I am working on a price comparison system which matches the best prices for a purchase (or an order) from exisiting purchase data.
The order is stored in multiple tables including order details (stores major items purchased: e.g., PC) and order sub-details (optional items purchased with the major items: e.g., speakers, backup device, webcam etc.).
There could be a number of major items in an order and each major item could have multiple related sub items. The other variables that affect the price include trade-ins if any, sales going on at the time of order, number of units etc.

Now, for any new configuration (major items/related sub items), the system should be able to return a list of previous purchases made with similar configurations, and similar variables (quatities, trade-ins etc). Even if the same model is not present, similar pcs by the same vendor should be considered. etc etc.

Questions:
Is this possible using Data mining?
If yes, which algorithm is recommended?

Also, can I assign/modify any kind of weights to certain variables (if same model: .6 ; if same model not available but pcs made by same manufacturer available: .3 ; by other manufacturers: .1)?

Any help will be greatly appreciated.

Thanks,
Jojy

This seems like a reasonable problem for data mining. I would recommend decision trees, neural nets, or logistic regression. There is no way in SS2k5 to weight attributes, however, you can simulate gross weighting in NN and LR by duplicating columns. E.g. if you have a column "model" which you want to weight twice as much as other columns, you duplicate it to "model" and "model1" with the same data inside.