Unless I'm missing something, it's very simple.

SELECT *
FROM #T
WHERE PrescriptionID < DuplicatePrescriptionID

Drew

Please use the SQL Code tags when posting SQL Code.  SQL Code is much easier to read when proper indentation is used, and the SQL Code tags preserve that indentation....

Here is a solution using LAG.

BEGIN TRY
  SELECT *
  INTO #mytable
  FROM (
      VALUES
  (1, CAST('2/26/2015 5:58' AS DATETIME), 'test1', 'test1',...

ROW_NUMBER(), RANK(), and DENSE_RANK() produce a monotonically increasing series within a partition with respect to the ORDER BY expressions.  That is, if your sort by partition expressions and then the...

Reported as spam.

Reported as spam.

Reported as spam.

When posting sample data and expected results, they must MATCH.  Your sample data contains two trade names: 'Alhidd Cold Store' and 'Store', and your expected results contain five names which...

This is not the code that the error is referring to.  The error specifically mentions a RETURN statement, and there are no RETURN statements in this code.

Drew