why I’m not able to use LIMIT and OFFSET in SQL Server stored procedure

I am trying to get MAX – 1 record using below query but if I use LIMIT and OFFSET in query it is throwing error. Any other way can use this query to get Max – 1 row? I am using SQL Server

UPDATE [dbo].[ORDER_DETAIL] 
SET LINE_STATUS_CODE = 'C' 
WHERE RATE_STRUCTURE = 'LIST' 
  AND RATE_CODE = 'PRIMARY' 
  AND SHIP_MASTER_CUSTOMER_ID = @ip_master_customer_id  
  AND [ORDER_NO] = @ip_order_id 
  AND ORDER_LINE_NO = (SELECT ORDER_LINE_NO 
                       FROM [dbo].[ORDER_DETAIL] 
                       WHERE RATE_STRUCTURE = 'LIST' 
                         AND ORDER_NO = @ip_order_id 
                         AND SHIP_MASTER_CUSTOMER_ID = @ip_master_customer_id 
                       LIMIT 1 OFFSET 1 
                       ORDER BY ORDER_LINE_NO DESC)

Answer

This is the exact syntax you should use for your inner select:

SELECT ORDER_LINE_NO 
FROM [dbo].[ORDER_DETAIL] 
WHERE RATE_STRUCTURE = 'LIST' 
AND ORDER_NO = @ip_order_id 
AND SHIP_MASTER_CUSTOMER_ID = @ip_master_customer_id 
ORDER BY ORDER_LINE_NO DESC
OFFSET 1 ROW
FETCH NEXT 1 ROW ONLY

And then your update statement will be like this:

UPDATE [dbo].[ORDER_DETAIL] SET LINE_STATUS_CODE ='C' 
WHERE RATE_STRUCTURE = 'LIST' 
AND RATE_CODE= 'PRIMARY' 
AND SHIP_MASTER_CUSTOMER_ID = @ip_master_customer_id
AND [ORDER_NO] = @ip_order_id  
AND ORDER_LINE_NO = (SELECT ORDER_LINE_NO 
                     FROM [dbo].[ORDER_DETAIL] 
                     WHERE RATE_STRUCTURE = 'LIST' 
                     AND ORDER_NO = @ip_order_id  
                     AND SHIP_MASTER_CUSTOMER_ID = @ip_master_customer_id
                     ORDER BY ORDER_LINE_NO DESC
                     OFFSET 1 ROW
                     FETCH NEXT 1 ROW ONLY);

Here is a small demo

P.S. SQL Server does not have LIMIT, you can use TOP or OFFSET with FETCH NEXT. If you want to use TOP you can not use the offset to start from second result or third result.