Method 1

SELECT t.*
FROM table t
INNER JOIN (SELECT id,MAX(salary) as MaxSal
            FROM table
            GROUP BY id
           )t1
On t1.id = t.id
AND t1.MaxSal = t.salary

Method 2 

SELECT name,id,salary
FROM (SELECT *,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY salary DESC) AS rn 
FROM table
)t
WHERE Rn=1

Method 3

SELECT t.*
FROM table t
CROSS APPLY (SELECT MAX(Salary) AS MaxSal
             FROM table
             WHERE id = id
             )t1
WHERE t.salary = t1.MaxSal

quote:

---

Originally posted by jj735301

Wow! Good stuff.

Can you help me with a query to derive:

4|x|12|10
3|t|11|10

from:
x|12
x|10
x|9
x|8
t|11
t|10
t|9

I need the cnt,id,max,next max

Hope that makes sense

Thank you

---