0

I am trying to rank data based off multiple factors and having trouble doing so. I want to rank each Batch and Material based off the most recent entry date and max value material doc item. (So 2 over 1 etc)

enter image description here

In the above sample data I would expect row 5 to be ranked 1 as it is the most recent date with the highest material doc item.

Current Logic:

RANK () OVER (PARTITION  BY BATCH, MATERIAL ORDER BY BATCH, MATERIAL , ENTRY_DATE  , MATERIAL_DOC_ITEM DESC ) AS RANK_NUM

Any guidance would be much appreciated

Chris Albert
  • 2,462
  • 8
  • 27
  • 31
tomfbsc
  • 45
  • 5
  • FYI, ordering on columns in your partition is pointless; all the values in that partition are going to be the same. `BATCH` and `MATERIAL` do not need to be in the `ORDER BY` clause. – Thom A May 18 '22 at 10:47

1 Answers1

0

You need DESC after the ENTRY_DATE as well to get most recent date. Also note no need to include batch and material in the order by as the ordering is within the partition

RANK () OVER (PARTITION  BY BATCH, MATERIAL ORDER BY ENTRY_DATE DESC, MATERIAL_DOC_ITEM DESC ) AS RANK_NUM
James Casey
  • 2,447
  • 1
  • 11
  • 19
  • Yes this is working perfectly. Was stuck for the last hour on this and didn't know I needed to declare the order twice. Thanks for all your help – tomfbsc May 18 '22 at 10:47
  • @tomfbsc, if the answer worked you should accept it, that helps future users who have a similar question quickly locate good solutions. Also, you're more likely to get people to invest time solving your future questions if you give credit for the good answers you get. – Robert Sheahan Jul 12 '22 at 15:09