View previous topic :: View next topic
|
Author |
Message |
muneer
New User
Joined: 06 Mar 2006 Posts: 3
|
|
|
|
I need solution to followin task:
let us say table has fields like subgroup and members
a subgroup may have more than one members
subgroup member
x 1
x 2
x 2
y 7
y 5
z 2
z 3
i need all subgroup with one member
Results
x 1
y 7
z 3 |
|
Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005 Posts: 1448 Location: Chicago, IL
|
|
|
|
What are the conditions based on which you are selecting MEMBER.
Regards,
Priyesh. |
|
Back to top |
|
|
muneer
New User
Joined: 06 Mar 2006 Posts: 3
|
|
|
|
Sorry for late
There was no search condition
only any one member for each subgroup i need |
|
Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005 Posts: 1448 Location: Chicago, IL
|
|
|
|
You can not say randomly to pick out any member, when SQL would run it'll check all the rows of a TABLE.
Check for an ICETOOL on Table extract if you just want to pick up first such member.
Regards,
Priyesh. |
|
Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
|
|
|
|
muneer,
This SQL will select the 'SUBGROUP' with the minimum 'MEMBER' value.
You indicated on your last post that ANY row would work. I created a SESSION.TEMP_TBL and loaded it with the values you suplied.
Code: |
SELECT DISTINCT SUBGROUP, MEMBER
FROM SESSION.TEMP_TBL TT
WHERE MEMBER =
(SELECT MIN(MEMBER)
FROM SESSION.TEMP_TBL TT2
WHERE TT2.SUBGROUP = TT.SUBGROUP
)
;
+--------------------+
| SUBGROUP | MEMBER |
+--------------------+
1_| X | 1 |
2_| Y | 5 |
3_| Z | 2 |
+--------------------+
|
Dave |
|
Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
|
|
|
|
OR,
Code: |
SELECT DISTINCT SUBGROUP, MIN(MEMBER) AS MEMBER
FROM SESSION.TEMP_TBL
GROUP BY SUBGROUP
;
+--------------------+
| SUBGROUP | MEMBER |
+--------------------+
1_| X | 1 |
2_| Y | 5 |
3_| Z | 2 |
+--------------------+
|
Dave |
|
Back to top |
|
|
|