|
View previous topic :: View next topic
|
| Author |
Message |
arvind.m
Active User
Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad
|
|
|
|
Hi all,
i have the below table
| Code: |
col1 col2 col3 col4
a 1 xx xx1
b 2 yy yy2
c 2 zz zzc
d 3 pp ppk
e 3 kk kkk
f 3 jj jjj |
and i want only one occurance of col2.
output:
| Code: |
col1 col2 col3 col4
a 1 xx xx1
b 2 yy yy2
d 3 pp ppk
|
i already asked this question but the solution was also provided my me. but was manipulated to get the result.
Is there any query to achive unique row for col2.
Thanks, |
|
| Back to top |
|
 |
bhairon singh rathore
New User
Joined: 19 Jun 2008
Posts: 91
Location: banglore
|
|
|
|
Hi Arvind,
For unique col2 which of the matching col1,col3,col4 should be retrieved in query. |
|
| Back to top |
|
|
arvind.m
Active User
Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad
|
|
|
|
| any column....i need only one occurance of col2... |
|
| Back to top |
|
|
bhairon singh rathore
New User
Joined: 19 Jun 2008
Posts: 91
Location: banglore
|
|
|
|
| Why dont you use DB2 - function distinct on col2 while quering from database |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
doesn't make sense, but here a possible solution:
| Code: |
| select min(col1), col2, min(col3), min(col4) from table1 group by col2 |
or if col1 is primary key
| Code: |
select t1.col1,t1.col2,t1.col3,t1.col4 from table1 t1
where t1.col1 = (select min(t2.col1) from table1 t2 where t1.col2=t2.col2) |
|
|
| Back to top |
|
|
arvind.m
Active User
Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad
|
|
|
|
what if input is like this....col1 has same value.
| Code: |
col1 col2 col3 col4
a 1 xx xx1
b 2 yy yy2
b 2 zz zzc
d 3 pp ppk
e 3 kk kkk
f 3 jj jjj |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
then col1 is not the primary key :p
What is the primary key of the table ? |
|
| Back to top |
|
|
raam2smart
New User
Joined: 31 May 2007
Posts: 19
Location: Chennai
|
|
|
|
Try this...
select * from t1 where col2 in(select distinct(col2) from t1)
Ramesh |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| raam2smart wrote: |
Try this...
select * from t1 where col2 in(select distinct(col2) from t1)
Ramesh |
uhm, NO.
This will give the same result as: select * from t1 |
|
| Back to top |
|
|
bhairon singh rathore
New User
Joined: 19 Jun 2008
Posts: 91
Location: banglore
|
|
|
|
Raam
Try your query before posting it on this site.
Not a Smart work 
Regards
Bhairon Singh Rathore |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| I'm afraid the only way you can use to achieve your expected result is using cursor. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
in DB2 9 you could use fetch first in a subselect.
something like this :
| Code: |
select C.* from
(select creator from sysibm.systables group by creator) A
,
(select * from sysibm.systables B where b.creator = a.creator
fetch first row only) C |
I can't test it, because we're not yet in NFM. |
|
| Back to top |
|
|
|
|
