[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

Maximum Record Selection

I have a table:

Product

This table is linked to another table:

Product Price

This table contains:
Price Date
Price

I need to be able to select the latest price for a product.
0
whittinga2
Asked:
whittinga2
  • 3
  • 3
  • 2
2 Solutions
 
mlmccCommented:
You are going to have to use a view/stored procedure in the database or a Crystal command

mlmcc
0
 
mlmccCommented:
The query will be something like

SELECT Product.ID, Product.Description, ...
(SELECT Price.Price  FROM Price WHERE Price.ProductID = Product.ID AND Price.PriceDate = Maximum(Price.PriceDate)
FROM Product

mlmcc
0
 
mlmccCommented:
Error in the SQL

SELECT Product.ID, Product.Description, ...
(SELECT Price.Price  FROM Price WHERE Price.ProductID = Product.ID AND Price.PriceDate = (SELECT Maximum(Price.PriceDate) FROM Price WHERE Price.ProductID = Product.ID ))
FROM Product

mlmcc
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
James0628Commented:
FWIW, you _could_ find the most recent price in a report, but it would probably be much more efficient to do it in the db query, as mlmcc suggested.  It would depend a lot on your data and the report, and what you wanted to do with that price (just display it, or use it in calculations).

 James
0
 
whittinga2Author Commented:
Cheers fellas. I will give it a go and get back to you.

Sorry for the delay.
0
 
whittinga2Author Commented:
Any idea why I've been given a syntax error?

SELECT *
(SELECT * FROM pricehistory WHERE pricehistory.productid = productdetails.productid AND pricehistory.priceeffectivefromdate =
(SELECT Maximum(pricehistory.priceeffectivefromdate) FROM pricehistory WHERE pricehistory.productid = productdetails.productid))
FROM productdetails
0
 
James0628Commented:
The subquery was meant to pull in the latest price (_only_ the price), and that would become another column in the main query.  You have "(SELECT * FROM pricehistory ...", so you're pulling in all of the columns from pricehistory.

 So, that should be

(SELECT your_price_column FROM pricehistory WHERE <etc.>


 If you could have more than one entry in pricehistory with the same date, then you should use something like TOP 1, to make sure that the subquery only returns one value:

(SELECT TOP 1 your_price_column FROM pricehistory WHERE <etc.>

 OTOH, if priceeffectivefromdate is a datetime, then that probably won't happen.  But I wanted to mention it anyway, just in case.


 You probably also want to set a name for that subquery column.

 And you need a "," at the end of the first line, to separate the other columns (*) and the subquery column.

 So, putting all of that together, maybe something like this:

SELECT *,
(SELECT your_price_column FROM pricehistory WHERE pricehistory.productid = productdetails.productid AND pricehistory.priceeffectivefromdate =
(SELECT Maximum(pricehistory.priceeffectivefromdate) FROM pricehistory WHERE pricehistory.productid = productdetails.productid)) AS latest_price
FROM productdetails


 Obviously, replace your_price_column with the actual column name.  And you can add the TOP 1 if you need it.

 James
0
 
whittinga2Author Commented:
Thanks to both of you. Points allocated.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now