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
 

 

Retrieve the first record in the duplicates

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
santhosh.kumar.vj

New User


Joined: 03 Aug 2011
Posts: 8
Location: Bangalore

PostPosted: Sun Nov 27, 2011 12:18 pm    Post subject: Retrieve the first record in the duplicates
Reply with quote

hi folks

I have a tables in this structure
SELECT * FROM X1;
---------+---------+---------+---------+---------+---------+---------+---------+
ENO ENAME ESAL DEPTNO MOBILE
---------+---------+---------+---------+---------+---------+---------+---------+
1. MADHAN........40000....777.........9876543212.
2. VINOTH.........40000....888.........8886543212.
3. ARAVIND.......40000....777.........7777743212.
4. XXX..............85000.....777.........7777743212.
DSNE610I NUMBER OF ROWS DISPLAYED IS 4

I want the output like this

---------+---------+---------+---------+---------+---------+---------+---------+
ENO ENAME ESAL DEPTNO MOBILE
---------+---------+---------+---------+---------+---------+---------+---------+
1. MADHAN........40000....777.........9876543212.
2. VINOTH.........40000....888.........8886543212.


I need a query to retrieve the first record in the duplicates considering the column DEPTNO.

Please help me to sort this problem.


Thanks in Advance
Back to top
View user's profile Send private message

xknight

Active User


Joined: 22 Jan 2008
Posts: 117
Location: Liberty city

PostPosted: Mon Nov 28, 2011 12:16 pm    Post subject:
Reply with quote

Hello,

Quote:
I want the output like this

---------+---------+---------+---------+---------+---------+---------+---------+
ENO ENAME ESAL DEPTNO MOBILE
---------+---------+---------+---------+---------+---------+---------+---------+
1. MADHAN........40000....777.........9876543212.
2. VINOTH.........40000....888.........8886543212.


Your output displays two retrieved records, but you asked for a query to retrieve the first record in the duplicates

Quote:
I need a query to retrieve the first record in the duplicates considering the column DEPTNO


Did you tried
Code:
FETCH FIRST ROW ONLY


Hope it helps
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Nov 28, 2011 2:30 pm    Post subject:
Reply with quote

I'm really curious how xknight is solving this with fetch first row only.

I would solve it this way:
Code:
select * FROM X1 A
where A.eno = (select min(b.eno) FROM X1 b where a.deptno = b.deptno)
Back to top
View user's profile Send private message
shibubalakrishnan

New User


Joined: 16 Oct 2009
Posts: 13
Location: Bangalore

PostPosted: Mon Nov 28, 2011 2:45 pm    Post subject:
Reply with quote

A cursor which has a "OPTIMIZE FOR 2 ROWS" will get the first 2 rows matching the requirement.

e.g.
Code:

EXEC SQL DECLARE C-S CURSOR FOR         
  SELECT SEQ                           
  FROM MYNAM WHERE NAME=:WS-NAME
  OPTIMIZE FOR 2 ROWS                   
END-EXEC.



-Shibu.T-
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Mon Nov 28, 2011 2:50 pm    Post subject:
Reply with quote

Hi shibubalakrishnan

Please do not provide the wrong information..

OPTIMIZE FOR 2 ROWS is not used for getting two rows which matches the requirement..

Check manuals for use of OPTIMIZE clause.

Regards,
Chandan
Back to top
View user's profile Send private message
shibubalakrishnan

New User


Joined: 16 Oct 2009
Posts: 13
Location: Bangalore

PostPosted: Mon Nov 28, 2011 3:00 pm    Post subject:
Reply with quote

Quote:
Please do not provide the wrong information..


Quote:
Check manuals for use of OPTIMIZE clause.

The OPTIMIZE FOR clause requests special optimization of the select-statement. The clause specifies that optimization is based on the assumption that the number of rows retrieved will not exceed n, where n is the value of the integer. If the clause is omitted, optimization is based on the assumption that all rows of the result table will be retrieved unless the FETCH FIRST clause is specified. If OPTIMIZE FOR is omitted and FETCH FIRST is specified, OPTIMIZE FOR integer ROWS is assumed, where integer is the value of FETCH FIRST.

The OPTIMIZE FOR clause does not limit the number of rows that can be fetched or affect the result in any way other than performance. In general, if you are retrieving only a few rows, use OPTIMIZE FOR 1 ROW to influence the access path that DB2 selects. For more information about using this clause, see DB2 Application Programming and SQL Guide.

I strongly believe that we can fetch 2 rows with a "OPTIMIZE for 2 rows"

-Shibu.T-
Back to top
View user's profile Send private message
xknight

Active User


Joined: 22 Jan 2008
Posts: 117
Location: Liberty city

PostPosted: Mon Nov 28, 2011 3:08 pm    Post subject:
Reply with quote

Hello,

Quote:
I'm really curious how xknight is solving this with fetch first row only.


Mis-understood the TS query i suppose icon_redface.gif

Thanks for pointing it out.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Nov 28, 2011 3:18 pm    Post subject:
Reply with quote

shibubalakrishnan wrote:
Quote:
The OPTIMIZE FOR clause does not limit the number of rows that can be fetched or affect the result in any way other than performance.

I strongly believe that we can fetch 2 rows with a "OPTIMIZE for 2 rows"

you can fetch 2000 rows with an "optimize for 2 rows" but it will never remove rows with identical deptno.
Back to top
View user's profile Send private message
shibubalakrishnan

New User


Joined: 16 Oct 2009
Posts: 13
Location: Bangalore

PostPosted: Mon Nov 28, 2011 3:26 pm    Post subject:
Reply with quote

ah. I'm sorry, I thought the 40000 is the key icon_redface.gif icon_redface.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Nov 28, 2011 3:30 pm    Post subject:
Reply with quote

shibubalakrishnan wrote:
ah. I'm sorry, I thought the 40000 is the key
I think you still don't get it. Which field is the key is irrelevant.
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Tue Nov 29, 2011 11:52 pm    Post subject: Reply to: Retrieve the first record in the duplicates
Reply with quote

Hi,

To my knowledge, there is no first row or last row as far as DB2 is concerned. If my knowledge is not correct please correct me.

For the above query, GROUP BY can be used to get a single row from the DEPT column. But it is not guaranteed that the row in the result set will satisfy your condition. To get the desired result, more filter is required(ENO, ESAL etc).

Regards
Raghu
Back to top
View user's profile Send private message
santhosh.kumar.vj

New User


Joined: 03 Aug 2011
Posts: 8
Location: Bangalore

PostPosted: Wed Nov 30, 2011 10:15 am    Post subject:
Reply with quote

Thanks guyc for ur inputs,the query is working fine
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Nov 30, 2011 4:44 pm    Post subject: Re: Reply to: Retrieve the first record in the duplicates
Reply with quote

Raghu navaikulam wrote:
Hi,

To my knowledge, there is no first row or last row as far as DB2 is concerned. If my knowledge is not correct please correct me.

For the above query, GROUP BY can be used to get a single row from the DEPT column. But it is not guaranteed that the row in the result set will satisfy your condition. To get the desired result, more filter is required(ENO, ESAL etc).

Regards
Raghu

True, there is no sequence in a db2 table. but I assumed he wants the row with the lowest primary key, ie. ENO.

a group by is not possible if you want more columns than only DEPTNO.
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 Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Updating the counters after eliminati... PANDU1 DFSORT/ICETOOL 12 Mon Nov 21, 2016 9:47 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts sort with previous record anatol DFSORT/ICETOOL 9 Thu Oct 06, 2016 2:36 am
No new posts Get Record count in summary record fo... Atul Banke DFSORT/ICETOOL 21 Fri Sep 23, 2016 4:17 pm


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