That syntax will not work in SQL Server. To get the row numbers use the ROW_NUMBER() window function.

This then requires an order by which determines the row order to be numbered. This order by should be unique so as to make the row_numbers deterministic each time you run the query.

e.g.

ROW_NUMBER() OVER (ORDER BY REFERENCE) as 'row_number',

or

ROW_NUMBER() OVER (ORDER BY REFERENCE, USER_DATA_NUMERIC_02) as 'row_number',

Also, you cannot use ROW_NUMBER() in a HAVING or WHERE clause, so should you need to filter on it, you must put it in a CTE.

;WITH CTE AS (

SELECT

CONSIGNMENT_TRACK_BUY_SUP_REFS_UNS.REFERENCE AS REFERENCE,

CONSIGNMENT_ALL_USER_DEFINES_UNS.USER_DATA_NUMERIC_02,

ROW_NUMBER() OVER (ORDER BY REFERENCE) as 'row_number',

from CONSIGNMENT_TRACK_BUY_SUP_REFS_UNS

join CONSIGNMENT_ALL_USER_DEFINES_UNS on CONSIGNMENT_TRACK_BUY_SUP_REFS_UNS.OPSREF$$ =

CONSIGNMENT_ALL_USER_DEFINES_UNS.OPSREF$$

where CONSIGNMENT_TRACK_BUY_SUP_REFS_UNS.OPSREF$$="ISL1600697"

)

SELECT * FROM CTE

WHERE 'row_number' = USER_DATA_NUMERIC_02