12,352,294 members (59,133 online)
Rate this:
See more:
My table is look like

```ID    PID     Value
1      1        1
2      1        2
3      1        3
4      1        4
5      2        1
6      2        2
7      2        3
8      3        1
9      3        2
10     4        1
11     4        2
12     5        1
13     6        1
14     7        1
15     7        2```

i want to get the records as per the MAX value; just see the example there is all PID exists with their MAX value and it'll look like following:

```ID    PID     Value
4      1        4
7      2        3
9      3        2
11     4        2
12     5        1
13     6        1
15     7        2```
How could i get this above following result using SQL?

Posted 8-Nov-12 22:21pm
Edited 8-Nov-12 22:43pm
v3
psychic6000 9-Nov-12 4:29am

whats special? on which bases you are selecting or ignoring rows?
biswarup88 9-Nov-12 4:30am

i want to get the records as per the MAX value; just see the example there is all PID exists with their MAX value
digimanus 9-Nov-12 11:06am

next time please tell which sql server you are using.

Rate this:

## Solution 1

Try:
```SELECT ID, PID, Value
FROM MyTable T1
WHERE Value =
( SELECT MAX(Value) FROM MyTable T2
WHERE T1.PID=T2.PID
)
ORDER BY T1.PID```
biswarup88 9-Nov-12 5:38am

Thanks a lot OriginalGriff
OriginalGriff 9-Nov-12 5:42am

You're welcome!
Maciej Los 9-Nov-12 11:46am

A 5!
I was a little bit late ;)
Rate this:

## Solution 2

It should works:
```SELECT T2.[ID], T1.[PID], T1.[Value]
FROM (
SELECT [PID], MAX([Value]) AS [Value]
FROM YourTable
GROUP BY [PID]
) AS T1 LEFT JOIN (
SELECT [ID], [PID], [VALUE]
FROM YourTable
) AS T2 ON T1.PID = T2.PID AND T1.[Value]=T2.[Value]```
biswarup88 9-Nov-12 5:38am

Thanks a lot Maciej Los
Maciej Los 9-Nov-12 11:46am

You're welcome!
Rate this:

## Solution 3

You can use the Row_Number function when using MS SQl Server 2005 and up

```select detail.ID, detail.PID, detail.Value
FROM
(
SELECT Row_Number() over (partition by PID, Value order by PID, Value desc) as rowno,
ID, PID, Value FROM MyTable
) as detail
where detail.rowno = 1```
OriginalGriff 9-Nov-12 11:56am

Very nice - I hadn't thought of that.

Top Experts
Last 24hrsThis month
 OriginalGriff 564 Sergey Alexandrovich Kryukov 301 Maciej Los 250 Dave Kreskowiak 150 Karthik Bangalore 149
 OriginalGriff 9,219 Sergey Alexandrovich Kryukov 6,299 Dave Kreskowiak 3,044 ppolymorphe 2,256 Karthik Bangalore 2,251