Solved

Maximum Record Selection

Posted on 2013-06-26
8
188 Views
Last Modified: 2013-06-28
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
Comment
Question by:whittinga2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39277961
You are going to have to use a view/stored procedure in the database or a Crystal command

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39278011
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 39278547
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 35

Expert Comment

by:James0628
ID: 39280779
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
 

Author Comment

by:whittinga2
ID: 39283648
Cheers fellas. I will give it a go and get back to you.

Sorry for the delay.
0
 

Author Comment

by:whittinga2
ID: 39283730
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
 
LVL 35

Accepted Solution

by:
James0628 earned 250 total points
ID: 39283816
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
 

Author Closing Comment

by:whittinga2
ID: 39283859
Thanks to both of you. Points allocated.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question