Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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