Results 1 to 7 of 7
  1. #1
    Join Date
    May 2013
    Posts
    2

    Question Unanswered: obtain article prices with one given date

    hi everyone!! i need to write an sql query but i need your help
    i have one table named test and four columns: order_id(integer), article_id(integer), price_date(date) and price(double)
    for example:

    order_id | article_id | price_date | price

    01 6 "2013-01-10" 67.35
    45 7 "2013-01-10" 108.01
    89 8 "2013-01-10" 144.18
    10 9 "2013-01-10" 181.19
    12 10 "2013-01-16" 29.53
    32 11 "2013-01-13" 20.5
    56 11 "2013-01-16" 20.7
    12 11 "2013-01-16" 20.8
    74 11 "2013-02-01" 20.91
    63 13 "2013-01-16" 66.1
    85 14 "2013-01-16" 51.9
    44 14 "2013-01-16" 51.2
    94 14 "2013-02-01" 51.06
    77 15 "2013-01-16" 16.3

    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"
    for example:
    entering the date '2013-01-16' should retrieve this:

    order_id | article_id | price_date | price

    01 6 "2013-01-10" 67.35
    45 7 "2013-01-10" 108.01
    89 8 "2013-01-10" 144.18
    10 9 "2013-01-10" 181.19
    12 10 "2013-01-16" 29.53
    56 11 "2013-01-16" 20.7
    63 13 "2013-01-16" 66.1
    85 14 "2013-01-16" 51.9
    77 15 "2013-01-16" 16.3

    i think you can help me...
    THANKS A LOT!!

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    This gives me the output you have in your OP.

    Code:
    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
         from test
         where price_date <= '2013-01-16'
     )
     Select order_id, article_id, price_date, price
       from cte
       where  RN = 1
    Last edited by LinksUp; 05-21-13 at 03:53. Reason: Replaced reserved word on alias

  3. #3
    Join Date
    May 2013
    Posts
    2

    Wink thanks i think that should work

    thanks i think that should work

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Not really; ROWNUM can't be used as a column alias. But, if you change it to something else (such as RN or MY_LEFT_FOOT or ...), it'll be OK.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    LinksUp, while it was nice of you to provide the solution we normally don't spoon feed homework. We just give hints and help them figure it out. That way they learn the information.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    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.

  7. #7
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Wow, that escalated quickly!
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •