View previous topic :: View next topic
|
Author |
Message |
srikanthrao
New User
Joined: 24 Jul 2007 Posts: 8 Location: Chennai
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
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 |
|
|
srikanthrao
New User
Joined: 24 Jul 2007 Posts: 8 Location: Chennai
|
|
|
|
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 |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006 Posts: 3156 Location: Tucson AZ
|
|
|
|
Interesting interview question....
How about a select/where that used the MOD 2 and the row number? |
|
Back to top |
|
|
srikanthrao
New User
Joined: 24 Jul 2007 Posts: 8 Location: Chennai
|
|
|
|
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 |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006 Posts: 3156 Location: Tucson AZ
|
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 631 Location: Wisconsin
|
|
|
|
There is no such thing as an odd or even row.... |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
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 |
|
Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 631 Location: Wisconsin
|
|
|
|
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 |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 631 Location: Wisconsin
|
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
srikanthrao
New User
Joined: 24 Jul 2007 Posts: 8 Location: Chennai
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
If this is the request then my query is right |
|
Back to top |
|
|
|