Only show DISTINCT rows

I have a query on a table that I need some help on. The code is attached below for you to view.

Here is a snapshot of my data:

ARCD03      RCNO03      FNAM03      SURN03      RCDT03      LECC03      AwardGiven
1      15      William      Benjamin      0      MA      1
1      16      Maria      Gibson      0      MA      2
1      16      Steven      Thomson      0      MA      2
1      17      Steven      Marshall      0      MA      1
1      18      Monica       Hand      0      MC      1
1      19      Forrester       McKelvie      0      MA      1
1      20      James      Flynn      0      MA      1
1      20      Scott      George      0      MA      1
1      21      Graeme      Taylor      0      MA      2
1      23      Kelly      Cruikshanks      0      MA      2
1      23      Stephen      Wales      0      MA      2
1      25      James      Robertson      0      MA      1

What I'm after is for some way for the query to lookup 2 fields here ARCD03 and RCNO03 and if there is a duplicate row then only show one of them.

So the data would look more like this:

ARCD03      RCNO03      FNAM03      SURN03      RCDT03      LECC03      AwardGiven
1      15      William      Benjamin      0      MA      1
1      16      Maria      Gibson      0      MA      2
1      17      Steven      Marshall      0      MA      1
1      18      Monica       Hand      0      MC      1
1      19      Forrester       McKelvie      0      MA      1
1      20      James      Flynn      0      MA      1
1      21      Graeme      Taylor      0      MA      2
1      23      Kelly      Cruikshanks      0      MA      2
1      25      James      Robertson      0      MA      1

(The above has a 3 rows removed from it as both ARCD03 and RCNO03 data matched)

I'm currently trying this with a derived table (basically I have another View that only looks up ARCD03 and RCNO03 for DISTINCT values and then this is added as a table View in another query that brings in the other data). This appear s to work but takes a very long time to execute probably due to the compexity of the problem I've handed it.

Any ideas how to simplify is appreciated.Start Free Trial

1:
2:
3:
4:
5:
6:
7:
8:

SELECT DISTINCT 
                      TOP (100) PERCENT dbo.Ptp03.ARCD03, dbo.Ptp03.RCNO03, dbo.Ptp03.FNAM03, dbo.Ptp03.SNAM03, dbo.Ptp03.RCDT03, dbo.Ptp03.LECC03, 
                      MAX(dbo.Ptp06.AWNO06) AS AwardGiven
FROM         dbo.Ptp03 LEFT OUTER JOIN
                      dbo.Ptp06 ON dbo.Ptp03.ARCD03 = dbo.Ptp06.ARCD06 AND dbo.Ptp03.RGNO03 = dbo.Ptp06.RGNO06
GROUP BY dbo.Ptp03.ARCD03, dbo.Ptp03.FNAM03, dbo.Ptp03.SNAM03, dbo.Ptp03.RCDT03, dbo.Ptp03.LECC03, dbo.Ptp03.RCNO03
HAVING      (dbo.Ptp03.ARCD03 IS NOT NULL) AND (MAX(dbo.Ptp06.AWNO06) IS NOT NULL)
ORDER BY dbo.Ptp03.ARCD03, dbo.Ptp03.RCNO03