Click here to Skip to main content
12,352,294 members (59,133 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL
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?
Please help me to solve this.

Thanks in advance.
Posted 8-Nov-12 22:21pm
Edited 8-Nov-12 22:43pm
v3
Comments
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: bad
 
good
Please Sign up or sign in to vote.

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
  Permalink  
Comments
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: bad
 
good
Please Sign up or sign in to vote.

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]
  Permalink  
Comments
biswarup88 9-Nov-12 5:38am
   
Thanks a lot Maciej Los
Maciej Los 9-Nov-12 11:46am
   
You're welcome!
Rate this: bad
 
good
Please Sign up or sign in to vote.

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
  Permalink  
Comments
OriginalGriff 9-Nov-12 11:56am
   
Very nice - I hadn't thought of that.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160621.1 | Last Updated 9 Nov 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100