IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to retrieve odd rows of a table


IBM Mainframe Forums -> Mainframe Interview Questions
Post new topic   Reply to topic
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
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

Global Moderator


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

PostPosted: Wed Aug 08, 2007 1:15 pm
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
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: 3156
Location: Tucson AZ

PostPosted: Wed Aug 08, 2007 3:27 pm
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
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: 3156
Location: Tucson AZ

PostPosted: Wed Aug 08, 2007 4:09 pm
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

Global Moderator


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

PostPosted: Wed Aug 08, 2007 4:47 pm
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
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

Global Moderator


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

PostPosted: Thu Aug 09, 2007 11:09 am
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
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
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
Reply with quote

As you have said here: 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

Moderator Emeritus


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

PostPosted: Thu Aug 09, 2007 10:31 pm
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
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

Global Moderator


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

PostPosted: Fri Aug 10, 2007 11:14 am
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> Mainframe Interview Questions

 


Similar Topics
Topic Forum Replies
No new posts how to send 10 rows at a time to output CICS 2
No new posts Read a flat file and update DB2 table JCL & VSAM 2
No new posts Search substring in internal table of... COBOL Programming 2
This topic is locked: you cannot edit posts or make replies. Write from flat file to db2 table Mainframe Interview Questions 5
No new posts Table Information - DB2 DB2 1
Search our Forums:

Back to Top