Row_Number where row = 1 not working

Question

I have this select statement:

SELECT MODATA.CITEM, MODATA.QTREQ, MODATA.UNMSR, MODATA.CDESC, MODATA.USRSQ, SLQNTY.LLOCN, SLQNTY.LQNTY, ROW_NUMBER() OVER (PARTITION BY CITEM ORDER By CITEM) as tr
FROM MODATA
LEFT OUTER JOIN SLQNTY ON SLQNTY.ITNBR = MODATA.CITEM
WHERE MODATA.ORDNO = 'M123456' AND SLQNTY.LQNTY >= '0'
ORDER BY MODATA.USRSQ, MODATA.CITEM

Which pulls this:

I want only the TR that = 1 to show... I tried adding this to my Where clause TR = '1' or TR = 1... it says it can't find that column? Why would that be and how can I fix it?

Answer 1

You need to use a subquery:

SELECT MS.*
FROM (SELECT MODATA.CITEM, MODATA.QTREQ, MODATA.UNMSR, MODATA.CDESC, MODATA.USRSQ, SLQNTY.LLOCN, SLQNTY.LQNTY,
             ROW_NUMBER() OVER (PARTITION BY CITEM ORDER By CITEM) as tr
      FROM MODATA JOIN
           SLQNTY
           ON SLQNTY.ITNBR = MODATA.CITEM
      WHERE MODATA.ORDNO = 'M123456' AND SLQNTY.LQNTY >= 0
     ) MS
WHERE tr = 1
ORDER BY MODATA.USRSQ, MODATA.CITEM

Your WHERE clause turns the outer join into an inner join, so I just made the query clearer by using the right join type. I also removed the single quotes from '0', because I'm guessing the column is a number.