what i need is when i enter JUST ONE given date, retrieve the list of ALL articles with JUST the price in which the date is equal or smaller than the indicated date, but just retrieve the nearest price (i mean just ONE price and one article...not repeated)
one detail is when there is two or more records that have equal date i need just the one that have the biggest "order_id"
entering the date '2013-01-16' should retrieve this:
with cte as
SELECT Order_id, Article_id, price_date, price,
ROW_NUMBER() over (partition by article_id order by price_date desc, order_id desc) as RN
where price_date <= '2013-01-16'
Select order_id, article_id, price_date, price
where RN = 1
Last edited by LinksUp; 05-21-13 at 02:53.
Reason: Replaced reserved word on alias
I have been vacillating between 'no reply' and going 'bat s**t crazy' as a response.
First, let me state that I pretty much figured it was a homework problem.
Test as a Table Name + 1st post + weird requirements = Homework
While I appreciate the need to guide the student to the answer to help them learn, I am not going to take it upon myself to be the homework police. If by me giving them an answer helps them get a better grade on the assignment, fine. These types of individuals will probably fail once they get in the real world. Is that my problem? I think not. People who look for shortcuts (or cheats) will find them. If not here, then somewhere else. (Almost as bad as drug addicts)
It seems that being either too nice or too nasty on this forum warrants a reprimand from someone. Apparently I have not learned how to convey the proper level of nastiness or niceness without getting called out for it.
If you want to monitor all postings and then post a warning as to whether it is a homework problem or not, please feel free to do so. But in my short time here, I have seen a few postings "flagged" as homework and the original poster is adamant that it is not. They simply provided a scaled down version of their problem.
Again, who am I to judge?
I saw a problem that I could answer, so I did. End of story.