quote:

---

select top 1 searchname, visit, L_Name, apptstart, ORD_DATE, OB_DATE, apptstop
from
(
select row_number() over (partition by pp.searchname order by l.name DESC) AS Seq,
pp.searchname, max(pv.visit)as visit,l.name as L_Name, ap.apptstart, ord.orderdate as ORD_DATE, ob.obsdate as OB_DATE, ap.apptstop
from patientprofile pp
inner join patientvisit pv on pp.patientprofileid = pv.patientprofileid
inner join LOCREG l on pv.FacilityId = l.FacilityId
inner join appointments ap on pv.patientvisitid = ap.patientvisitid
inner join document d on pp.pid = d.pid and pv.patientvisitid = d.patientvisitid
inner join orders ord on d.pid = ord.pid
inner join obs ob on d.pid = ob.pid
inner join obshead oh on ob.hdid = oh.hdid
group by pp.searchname,l.NAME, ap.apptstart, ord.orderdate, ob.obsdate, ap.apptstop
)t
where Seq=1
order by searchname

---

select top 1 searchname, visit, L_Name, apptstart, ORD_DATE, OB_DATE, apptstop
from
(
select TOP (10) row_number() over (partition by pp.searchname order by l.name DESC) AS Seq,
pp.searchname, max(pv.visit)as visit,l.name as L_Name, ap.apptstart, ord.orderdate as ORD_DATE, ob.obsdate as OB_DATE, ap.apptstop 
from patientprofile pp

SELECT row_count
FROM   sys.dm_db_partition_stats
WHERE  OBJECT_NAME(OBJECT_ID) = 'YourTableNameHere'

quote:

---

edit: nevermind i found the solution

---