Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Unique row.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
arvind.m

Active User


Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad

PostPosted: Thu Oct 29, 2009 5:52 pm    Post subject: Unique row.
Reply with quote

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
View user's profile Send private message

bhairon singh rathore

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Thu Oct 29, 2009 6:20 pm    Post subject:
Reply with quote

Hi Arvind,
For unique col2 which of the matching col1,col3,col4 should be retrieved in query.
Back to top
View user's profile Send private message
arvind.m

Active User


Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad

PostPosted: Thu Oct 29, 2009 6:22 pm    Post subject:
Reply with quote

any column....i need only one occurance of col2...
Back to top
View user's profile Send private message
bhairon singh rathore

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Thu Oct 29, 2009 6:28 pm    Post subject:
Reply with quote

Why dont you use DB2 - function distinct on col2 while quering from database
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Oct 29, 2009 6:30 pm    Post subject:
Reply with quote

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
View user's profile Send private message
arvind.m

Active User


Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad

PostPosted: Thu Oct 29, 2009 6:44 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Oct 29, 2009 6:51 pm    Post subject:
Reply with quote

then col1 is not the primary key :p

What is the primary key of the table ?
Back to top
View user's profile Send private message
raam2smart

New User


Joined: 31 May 2007
Posts: 19
Location: Chennai

PostPosted: Fri Oct 30, 2009 12:54 pm    Post subject:
Reply with quote

Try this...

select * from t1 where col2 in(select distinct(col2) from t1)

Ramesh
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Oct 30, 2009 1:14 pm    Post subject:
Reply with quote

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
View user's profile Send private message
bhairon singh rathore

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Fri Oct 30, 2009 1:42 pm    Post subject:
Reply with quote

Raam
Try your query before posting it on this site.

Not a Smart work icon_lol.gif icon_lol.gif

Regards
Bhairon Singh Rathore
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Fri Oct 30, 2009 5:32 pm    Post subject:
Reply with quote

I'm afraid the only way you can use to achieve your expected result is using cursor.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Nov 02, 2009 2:27 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Unique sort situation.... dbecker SYNCSORT 6 Wed Apr 27, 2016 8:53 pm
No new posts Unique Record Count anandhan87 DFSORT/ICETOOL 1 Tue Feb 09, 2016 4:06 pm
No new posts unique 8 character token Pedro CLIST & REXX 9 Fri May 08, 2015 10:59 pm
No new posts Count unique values in a column based... Deepakgoyal2005 JCL & VSAM 4 Fri Jul 25, 2014 5:49 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us