0

I have a table that looks like this

Invoice |Line # |Item # |Price Per
1       |1      |11     |5.00
1       |2      |22     |10.00
2       |1      |11     |5.00
2       |2      |22     |12.00
3       |1      |11     |5.00
4       |1      |11     |6.00

I am trying to get the last selling price of an item.

How do I run a script that yields the following results?

Invoice |Line # |Item # |Price Per
2       |2      |22     |12.00
4       |1      |11     |6.00

I am using this script to compare to the current selling price.

Thanks

Walter Bacon
  • 3
  • 1
  • Martin, Can you link to the post that this question is a duplicate of? I've looked everywhere for an answer and haven't found a similar solution that works. – Walter Bacon Jun 03 '16 at 21:27
  • This is a standard "greatest n per group" question ([of more than 1,000 on StackOverflow](http://stackoverflow.com/questions/tagged/greatest-n-per-group?sort=active&pageSize=50)) The [accepted answer](http://stackoverflow.com/a/6841644/73226) in the duplicate question linked uses a row_number solution. Other alternatives are also suggested in other answers. – Martin Smith Jun 03 '16 at 21:32

1 Answers1

0

Assuming invoice and line define the ordering, then the traditional method uses row_number():

select t.*
from (select t.*,
             row_number() over (partition by item order by invoice desc, line desc) as seqnum
      from t
     ) t
where seqnum = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786