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
 

 

Need to fetch full row for the particular fields distinct va

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Fri Feb 13, 2009 1:03 am    Post subject: Need to fetch full row for the particular fields distinct va
Reply with quote

This is the table :

Code:
Dept Section Class   
===   =====  ==== 
1     A      X         
1     A      Y
1     B      X
2     A      X
2     B      X
3     A      X
3     A      Y
3     A      Z


I want to fetch all the coloumns for the first records of distinct Dept.

So the O/P will be:

Code:
Dept Section Class   
===   =====  ==== 
1     A      X 
2     A      X
3     A      X


Please help me out.
Back to top
View user's profile Send private message

Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Fri Feb 13, 2009 10:44 am    Post subject:
Reply with quote

What have you tried so far?
Back to top
View user's profile Send private message
km_abdullah

New User


Joined: 03 Nov 2008
Posts: 60

PostPosted: Fri Feb 13, 2009 12:27 pm    Post subject:
Reply with quote

Hi debasis misra,

You may have to add a unique column to the table say 'col1' which should be in ascending order and can achieve your requirement. Try below -

Code:
SELECT * FROM TABLE1 WHERE col1 IN (SELECT MIN(col1)
FROM TABLE1 GROUP BY DEPT);                                     
Back to top
View user's profile Send private message
DB2 Guy

New User


Joined: 28 Oct 2008
Posts: 98
Location: Cubicle

PostPosted: Fri Feb 13, 2009 12:52 pm    Post subject: Reply to: Need to fetch full row for the particular fields d
Reply with quote

How about SELECT DISTINCT?
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2276
Location: @my desk

PostPosted: Fri Feb 13, 2009 1:19 pm    Post subject: Re: Reply to: Need to fetch full row for the particular fiel
Reply with quote

DB2 Guy wrote:
How about SELECT DISTINCT?
The OP needs all the three columns in output.
Debasis Misra wrote:
I want to fetch all the coloumns for the first records of distinct Dept.
By first record, if you meant the 'first' Class of the 'first' Section in ascending alphabetical order , how about trying this?
Code:
  SELECT DEPT
        ,MIN(SECTION)
        ,MIN(CLASS)
    FROM TABLE1
GROUP BY DEPT
Back to top
View user's profile Send private message
km_abdullah

New User


Joined: 03 Nov 2008
Posts: 60

PostPosted: Fri Feb 13, 2009 3:00 pm    Post subject:
Reply with quote

Hello Arun,

Is there a way to add a dummy column to the table instead of altering table with the column containing values in ascending order, thereby we can fetch the 'first' record of each distinct DEPT using MIN function & GROUP BY class?
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2276
Location: @my desk

PostPosted: Fri Feb 13, 2009 3:02 pm    Post subject:
Reply with quote

Mohamed Abdullah

I dont think there's a "first" or "last" or an "nth record" for a DB2 table.
Back to top
View user's profile Send private message
km_abdullah

New User


Joined: 03 Nov 2008
Posts: 60

PostPosted: Fri Feb 13, 2009 3:15 pm    Post subject:
Reply with quote

Hi Arun,
I was referring to this Topics's requirement of fetching first of the distinct records of DEPT.
When we add a dummy column say 'column1' containing values from 1 to 8 the table would like below -

Code:
Dept Section Class column1
===   =====  ====  =====
1     A        X     1         
1     A        Y     2
1     B        X     3
2     A        X     4
2     B        X     5
3     A        X     6
3     A        Y     7
3     A        Z     8


After this we can use MIN & GROUP BY as below to get our requirement.

Code:
SELECT * FROM TABLE1 WHERE column1 IN (SELECT MIN(column1)
FROM TABLE1 GROUP BY DEPT);     


Is it possible to add such dummy column without altering the Table.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2276
Location: @my desk

PostPosted: Fri Feb 13, 2009 9:04 pm    Post subject:
Reply with quote

Mohamed Abdullah,

Evenif the OP mentioned that he needs the "first records of distinct Depts", I believe it's not exactly doable as the concept is entirely different when it comes to a DB2 table. And you might end up with different results for the same sql.
Back to top
View user's profile Send private message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Fri Feb 13, 2009 11:04 pm    Post subject:
Reply with quote

Thanks Arun!!!!!!!
your concept of using MIN function is served my purpose....
really thanks a lot!!!!!!!!!!!!
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 Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts Sum Fields ballaswaroop DFSORT/ICETOOL 4 Tue May 02, 2017 11:07 am
No new posts Full health checks of IMS databases ashek15 IMS DB/DC 1 Sat Apr 01, 2017 8:34 am
No new posts DFSORT MUL FIELDS tspr52 DFSORT/ICETOOL 16 Fri Mar 03, 2017 11:53 pm
No new posts Sort two file having same field, one ... himanshu malik DFSORT/ICETOOL 7 Thu Feb 02, 2017 10:09 am


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