If i understand you correctly, you need to use
ROW_NUMBER()[
^] ranking function.
If
lineNum
starts from
0
try this:
SELECT productId , lineNum, price, discount, toDate, fromDate
FROM (
SELECT productId , lineNum, price, discount, toDate, fromDate, ROW_NUMBER() OVER(PARTITION BY productId ORDER BY lineNum)
FROM TableName
WHERE fromDate >= @fromDate AND toData <= @toDate AND lineNum > 0
) AS T
WHERE RowNo=1