USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(DiscountOption_ID int, Discount_ID int, QTY int, Discount_Percentage int)
GO

INSERT INTO myTable99(DiscountOption_ID, Discount_ID, QTY, Discount_Percentage)
SELECT 1, 1, 1,  0  UNION ALL
SELECT 2, 1, 3,  10 UNION ALL
SELECT 3, 1, 5,  15 UNION ALL
SELECT 4, 1, 10, 20
GO

DECLARE @Order Int
SELECT @Order = 8

    SELECT *
      FROM (    SELECT Discount_ID, MAX(QTY) AS MAX_QTY
		  FROM myTable99
	         WHERE QTY < @Order
 	      GROUP BY Discount_ID ) AS x
      JOIN myTable99 y
	ON  x.Discount_Id = y.Discount_Id
       AND x.MAX_QTY = y.QTY
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO