You can do this in T-SQL, but it gets complicated very quickly.  I would recommend that you use a reporting tool such as SSRS to produce these kinds of reports.

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...