Click here to Skip to main content
Rate this: bad
good
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 at 9-Nov-12 4:29am
   
whats special? on which bases you are selecting or ignoring rows?
biswarup88 at 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 at 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 at 9-Nov-12 5:38am
   
Thanks a lot OriginalGriff
OriginalGriff at 9-Nov-12 5:42am
   
You're welcome!
Maciej Los at 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 at 9-Nov-12 5:38am
   
Thanks a lot Maciej Los
Maciej Los at 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 at 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)



Advertise | Privacy | Mobile
Web04 | 2.8.141015.1 | Last Updated 9 Nov 2012
Copyright © CodeProject, 1999-2014
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