Teradata SQL PDCR query- filtering out to get only Unique Queries with High CPU rank ( Database Performance tuning )

Question

I am running a standard Query joining PDCRLogtbl with PDCRSQLTbl for pulling up high impact CPU. I am sure a similar one has been used / seen before , most places . Nothing exiting about it

    SELECT  
    RANK(ImpactCPU) AS CPURank
   ,USERNAME
   ,sessionid
   ,AcctString 
   ,/*--including additionally */ Hashrow ( qrytext)  Unique_SQL_ID
    /* qrytext comes from SQLtbl.SQLTextinfo if available or its    logtbl.querytext*/  
   <col list>
     from 
    (SELECT a.USERNAME
      , sessionid
    --  , a.logdate                 
      , a.ProcId
      , a.QueryId
      , a.expandacctstring
       <calculations for CPU skew, Impact CPU , other skew's here>
      from PDCRinfo.DBQLogtbl
      where <filters here> 
      group by clause 
      ) a 
      LOJ ( sel < col list> from FROM   PDCRInfo.DBQLSQLTbl s 
      where <filters here> ) s on <join cols >
      order by  CPURank  asc ,  spoolusage desc

o/p is something like this

CPUrank Username <other cols> impactCPU Querytext  Unique_SQL_ID
1       JohnD                 50000     Sel foobar   1F-0C-A1-EB
5       JaneD                 60000     sel yadaya   A9-CE-55-1D
6       JohnD                 35000     Sel foobar   1F-0C-A1-EB
9       JohnD                 25000     Sel foobar   1F-0C-A1-EB
10      BobD                  24000     sel Daddy    6E-1C-18-08
11      JaneD                 23000     sel yadaya   A9-CE-55-1D
12      JohnD                 22500     Sel foobar   1F-0C-A1-EB
15      GeorgeD               22400     sel holahu   9B-4A-D1-F4

Here the SAME Unique_SQL_ID shows up in varying spots 1 , 6 and 9.I just want the 1st Instance - one with the highest CPU rank to show up and rest of 'em ignored. After all its the same SQL being run over on different occasions.
Desired

CPUrank Username <other cols> impactCPU Querytext  Unique_SQL_ID
    1       JohnD                 50000     Sel foobar   1F-0C-A1-EB
    5       JaneD                 60000     sel yadaya   A9-CE-55-1D
    10      BobD                  24000     sel Daddy    6E-1C-18-08
    15      GeorgeD               22400     sel holahu   9B-4A-D1-F4

What should I be doing ? maybe put in a qualifying or some similar filter ... some sort of an OLAP function to pass those rows through that will filter em out. I guess I could do it through an extra pass of all those rows through a dt but wanted to see if there's a simple filter I can stick in there that can do the job just as well
Thanks !

Answer 1

Adding this filter at the end of the query , will give the fix .

  qualify 
        ROW_NUMBER() OVER (PARTITION BY Unique_SQL_ID
                               ORDER BY impactCPU DESC
                          )
                             = 1

The Original answer elegantly gave the row_num () over (PARTITION BY Unique_SQL_ID ORDER BY impactCPU DESC ) which is exactly what I was looking for. But this being an Ordered Analytical Function - you can't stick it in the where clause or having either. This is just a minor modification. The gist of the answer given by MatBailie is abs. right . Thanks Mat