TSQL-Join on own table, selecting the latest row only

I have a table tbl_Partner (shortened) with two "duplicate" partners:

RecordID (GUID) | RISEID (varchar(40)) | UpdateCounter(int) | etc. ...
-----------------------------------------------------------
12345678-9012-3456-7890-123456789012 | RISE123 | 0
87564321-9012-3456-7890-123456789012 | RISE123 | 1
-- other partners with different updatecounters etc.

Now I need a view which has the latest data for each partner (highest UpdateCounter) but the first RecordID (lowest UpdateCounter)

I tried this SQL already:

开发者_开发技巧select 
(select top 1 recordid from tbl_partner p2 where p2.riseid=p1.riseid order by p2.updatecounter ASC) as RecordID
, riseid, updatecounter
from tbl_partner p1
order by riseid

I get:

RecordID (GUID) | RISEID (varchar(40)) | UpdateCounter(int)
-----------------------------------------------------------
12345678-9012-3456-7890-123456789012 | RISE123 | 0 --(old data)
12345678-9012-3456-7890-123456789012 | RISE123 | 1 --(new correct data with the first RecordID)
-- other rows/columns omitted

How can I select the latest row for each RecordID/RiseID only? (UpdateCounter=1 in this example)

For the record: UpdateCounter can be something else than 1 of course and there are other datasets with different values. So I cannot use a simple WHERE updatecounter>0.

---

SELECT t1.RecordID,t1.RISEID,t2.UpdateCounter
FROM
   tbl_Partner t1
      inner join
   tbl_Partner t2
      on
          t1.RISEID = t2.RISEID
      left join
   tbl_Partner t1_anti
      on
          t1.RISEID = t1_anti.RISEID and
          t1_anti.UpdateCounter < t1.UpdateCounter
      left join
   tbl_Partner t2_anti
      on
          t1.RISEID = t2_anti.RISEID and
          t2_anti.UpdateCounter > t2.UpdateCounter
where
    t1_anti.RISEID is null and
    t2_anti.RISEID is null

It's not clear if you want rows to appear for items where only a single row has a particular RISEID. If not, add an extra condition into the join between t1 and t2, such that the UpdateCounter is higher on t2.

The two outer joins are to ensure that there is no earlier row than t1 (t1_anti) and no later row than t2 (t2_anti). In the WHERE clause, we ensure that those joins were unsuccessful.

---

Alternate one, using slightly more modern style:

;With OrderedRows as (
    Select RecordID,RISEID,UpdateCounter,
      ROW_NUMBER() OVER (PARTITION BY RISEID ORDER BY UpdateCounter ASC) as rnEarly,
      ROW_NUMBER() OVER (PARTITION BY RISEID ORDER BY UpdateCounter DESC) as rnLate
    from tbl_Partner
)
SELECT t1.RecordID,t1.RISEID,t2.UpdateCounter
FROM
   OrderedRows t1
       inner join
   OrderedRows t2
       on
            t1.RISEID = t2.RISEID
WHERE
    t1.rnEarly = 1 and
    t2.rnLate = 1

which may require fewer scans on the table.

---

Select Max UpdateCounter

 select distinct(
 (select top 1 recordid from tbl_partner p2 where p2.riseid=p1.riseid p2.updatecounter ASC
 ) )as RecordID,(select  Max(updatecounter) from tbl_partner p2 where p2.riseid=p1.riseid   ) as MaxUpdateCounter , riseid  from tbl_partner p1