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
 

 

How to retrieve odd rows of a table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions
View previous topic :: :: View next topic  
Author Message
srikanthrao

New User


Joined: 24 Jul 2007
Posts: 8
Location: Chennai

PostPosted: Wed Aug 08, 2007 10:40 am    Post subject: How to retrieve odd rows of a table
Reply with quote

hi,

can any one please give me the sql query which is able to retrieve only the odd rows of a table.

Srikanth.
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1531
Location: Andromeda Galaxy

PostPosted: Wed Aug 08, 2007 1:15 pm    Post subject:
Reply with quote

Hi srikanthrao,

Did you try on this if so tell me what you have worked on this so far so that we can help you from that point.
Back to top
View user's profile Send private message
srikanthrao

New User


Joined: 24 Jul 2007
Posts: 8
Location: Chennai

PostPosted: Wed Aug 08, 2007 3:09 pm    Post subject:
Reply with quote

hi prem,

i haven't tried i do not know any query which suits this requirement.....

so i posted a request to know whether we can hav a query or not for this one.


Srikanth
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Wed Aug 08, 2007 3:27 pm    Post subject:
Reply with quote

Interesting interview question....
How about a select/where that used the MOD 2 and the row number?
Back to top
View user's profile Send private message
srikanthrao

New User


Joined: 24 Jul 2007
Posts: 8
Location: Chennai

PostPosted: Wed Aug 08, 2007 3:59 pm    Post subject:
Reply with quote

hi william,

plz look at this query

select * from table where row_id mod 2 = 1;

can we write the query like this........
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Wed Aug 08, 2007 4:09 pm    Post subject:
Reply with quote

The syntax looks wrong, have you looked at the SQL Reference manual?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1531
Location: Andromeda Galaxy

PostPosted: Wed Aug 08, 2007 4:47 pm    Post subject:
Reply with quote

FOR ODD ROWS
---------------------



Code:


 SELECT ID_NO, ROW#+1 FROM XXXX.TBA  TB1,   
   TABLE (SELECT COUNT(*)  AS ROW#             
   FROM XXXX.TBA TB2                       
   WHERE TB2.ID_NO < TB1.ID_NO  ) AS TEMP_TAB
   WHERE MOD(ROW#,2)=0                         
   ORDER BY 1;                                 


Where ID_NO is my key field

FOR EVEN
------------

change




Code:

WHERE MOD(ROW#,2)=1


in the same code

Hope this helps
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Aug 09, 2007 5:15 am    Post subject:
Reply with quote

There is no such thing as an odd or even row....
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1531
Location: Andromeda Galaxy

PostPosted: Thu Aug 09, 2007 11:09 am    Post subject:
Reply with quote

stodolas,

His requirement was to fetch odd rows seperately from the table.So I helped him with the sample code which will help him attain that.Tahts it icon_biggrin.gif
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Aug 09, 2007 6:54 pm    Post subject:
Reply with quote

premkrishnan: Define an odd row.... put an order by on your select, then your odd rows may become even rows based on the new sort criteria. There are no odd or even rows in a database. And an "odd" row could become an even row if you insert something in. Your result set would constantly be in changing state.

Example:

Start with this small example table with Col A being the primary key.
Code:

Col A  Col B  "Row number"
B  Some data |  1
D  Some data |  2


Now insert C into Col A, so now we have this small table, and whoops your even row became odd.
Code:

Col A  Col B  "Row number"
B  Some data |  1
C  Some data |  2
D  Some data |  3


Now insert A into Col A, so now we have this small table, and every single row beyond A has flipped their odd/even state.
Code:

Col A  Col B  "Row number"
A  Some data |  1
B  Some data |  2
C  Some data |  3
D  Some data |  4


As I have shown, you can't guarentee your result set when thinking odd/even rows. Any concept of Odd/Even rows is deeply flawed when you are looking at a database table.

-Steve
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Aug 09, 2007 7:11 pm    Post subject:
Reply with quote

Odd, Even, First, Last row who comes up with these stupid ideas. Odd and Even sounds like he is trying to get a sample for testing.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Aug 09, 2007 7:39 pm    Post subject:
Reply with quote

As you have said here: http://ibmmainframes.com/viewtopic.php?p=92141#92141

Order can never be assured unless you use an ORDER BY, but even with using order by, odd/even is still variable with every query.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Aug 09, 2007 10:31 pm    Post subject:
Reply with quote

Hello,

Quote:
who comes up with these stupid ideas

From what i can tell, some of these questions are asked by interviewers not to get an actual answer, but to help them determine the knowledge level of the interviewee. A more senior interviewee would tell them that the question stated does not apply to normal database processing. Someone not so sure of themself would possible get "stuck" trying to provide a "real" answer. To me, this type of question falls in the realm of "trick questions" and i believe they have no place in an interview. . .

I'm curious if this was an interview question (in which case i'll be happy to move this to Interview Questions) or if TS has some requirement we do not yet understand. . .
Back to top
View user's profile Send private message
srikanthrao

New User


Joined: 24 Jul 2007
Posts: 8
Location: Chennai

PostPosted: Fri Aug 10, 2007 11:08 am    Post subject:
Reply with quote

hi dick ,

Yes this question was asked to my friend in an interview.

odd rows in sense was to fetch alternate rows from a table.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1531
Location: Andromeda Galaxy

PostPosted: Fri Aug 10, 2007 11:14 am    Post subject:
Reply with quote

If this is the request then my query is right icon_wink.gif
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 -> Mainframe Interview Questions All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm


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