Group problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group problem

Author

Topic

mavershang
Posting Yak Master

111 Posts

Posted - 2009-03-19 : 13:36:45

Hi all. Here is my question:
the table is like:

pos1 pos2 score
9 20 1.2
9 25 2
10 18 1.3
10 20 1.32
11 23 1.22
20 41 3.1
20 102 1.4
...

Now I want to group those rows share the same positions. For example, group 9:20(row 1), 10:20(row 4) and 20:41(row x) together because they contain the same position "20", and get the max(score) in that group.

Anyone could help me figure it out?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 13:42:18

[code]
SELECT posval,MAX(score)
FROM
(
SELECT posval,postype,score
FROM table m
UNPIVOT (posval FOR postype IN ([pos1],[pos2]))u
)t
GROUP BY posval
[/code]

mavershang
Posting Yak Master

111 Posts

Posted - 2009-03-19 : 13:46:17

Thanks. So helpful and fast response.

quote:

Originally posted by visakh16


SELECT posval,MAX(score)
FROM
(
SELECT posval,postype,score
FROM table m
UNPIVOT (posval FOR postype IN ([pos1],[pos2]))u
)t
GROUP BY posval

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 13:47:21

welcome

mavershang
Posting Yak Master

111 Posts

Posted - 2009-03-19 : 14:16:28

I do not know much about pivot. Here is a little bit more further.
how should I do if I want to show the pairing position associated with the shared element in the result like

if "20" is the sharing element, then show:

20 41 3.1
instead of just
20 3.1

Thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 14:54:47

[code]
SELECT posval,COALESCE(NULLIF(pos1,posval),pos2),score
FROM
(
SELECT posval,postype,score,DENSE_RANK() OVER (PARTITION BY posval ORDER BY score DESC) AS Seq,
MAX(CASE WHEN postype='pos1' THEN posval ELSE NULL END) OVER (PARTITION BY score) AS pos1,
MAX(CASE WHEN postype='pos2' THEN posval ELSE NULL END) OVER (PARTITION BY score) AS pos2
FROM table m
UNPIVOT (posval FOR postype IN ([pos1],[pos2]))u
)t
WHERE posval=20
AND Seq=1

[/code]

mavershang
Posting Yak Master

111 Posts

Posted - 2009-03-19 : 17:55:03

Thank you so much.

quote:

Originally posted by visakh16


SELECT posval,COALESCE(NULLIF(pos1,posval),pos2),score
FROM
(
SELECT posval,postype,score,DENSE_RANK() OVER (PARTITION BY posval ORDER BY score DESC) AS Seq,
MAX(CASE WHEN postype='pos1' THEN posval ELSE NULL END) OVER (PARTITION BY score) AS   pos1,
MAX(CASE WHEN postype='pos2' THEN posval ELSE NULL END) OVER (PARTITION BY score) AS   pos2
FROM table m
UNPIVOT (posval FOR postype IN ([pos1],[pos2]))u
)t
WHERE posval=20
AND Seq=1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 09:32:43

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources