Get rows of only latest (overall) load

Here's my table:

EmpID | Rating | LoadID
-----------------------
  1       5       开发者_Python百科 100
  1       7        101
  1       8        102
  2       6        100
  2       6        101
  3       4        102

I need to select only rows for the latest (overall) LoadID. Based on the above the latest(overall) LoadID is 102 so I need to have this result set:

EmpID | Rating | LoadID
-----------------------
  1        8      102
  3        4      102

---

Here's a sample of doing it with a CTE:

with RankCTE as
(
select
    EmpID
  , Rating
  , LoadID
  , Rank() over (order by LoadID desc) as R
from TABLE
)

select
    EmpID
  , Rating
  , LoadID
from RankCTE
where R = 1

---

You could declare a variable or use a nested query:

The following is untested and might have syntax errors:

SELECT
    t.[EmpId]
    ,t.[Rating]
    ,t.[LoadID]
FROM
    yourTable t
WHERE
    LoadId = (
    SELECT
        TOP 1 t.[LoadID]
    FROM
        yourTable t
    ORDER BY t.[LoadID] DESC
    )

---

Here goes the same using a CTE

;WITH TblCTE as(
SELECT  [EmpId],[Rating],[LoadID],RANK() over (order by [LoadID] DESC) r
FROM    yourTable)
SELECT  * FROM TblCTE WHERE r=1

---

You can do this

declare @T table (EmpID int, Rating int, LoadID int)
insert into @T values
(  1,       5,        100),
(  1,       7,        101),
(  1,       8,        102),
(  2,       6,        100),
(  2,       6,        101),
(  3,       4,        102)

select T.*
from @T as T
where
  LoadID in (select max(LoadID)
             from @T)