SQL Query

How I can get odd rows from the table.

such as:

1st row
3rd row
5th row
7th row
.
.
.
.
so on

Any help using emp table.
Thanks

Hi,

you could proceed as follows:

1.) alter table scott.emp by adding a new column

new_col | empno| job...
--------------------------
| 7566| Manager..............

2.) update emp by inserting 1,2,...in this new column.

new_col | empno| job...
--------------------------
1 | 7566| Manager..............
2 | 7590| Clark..............
...

3.) select * from scott.emp where MOD(new_col,2) != 0;

...this returns the rows where the numbers in new_col modulo 2 are not 0, i.e. the odd rows.


Cheers,
Dan1

another way may be using rownum..

select * from emp where rownum = 1


put it in procedure use cursor etc.,

row_num := 1

row_num := row_num + 2



select * from emp where rownum = :row_num;


This may help

I have to use only SQL not Pl/sql.

I don't why can't you use pl/sql even from front end apps, they can call stored procs and they can results ..

Here is another solution ..

Create a view on table test having columns test1,test2 ..

syntax:create view test1 as select rownum id,test1,test2 from test ;

select * from test1 where mod(id,2) <> 0;


Good luck


Raghu

Thanks for your solution. I will test it and let you know.

Thanks.

Hi,
just another way using rownum:

scott@oracle> select rownum rn, empno, ename from emp;



        RN      EMPNO ENAME

---------- ---------- ----------

         1       7369 SMITH

         2       7499 ALLEN

         3       7521 WARD

         4       7566 JONES

         5       7654 MARTIN

         6       7698 BLAKE

         7       7782 CLARK

         8       7788 SCOTT

         9       7839 KING

        10       7844 TURNER

        11       7876 ADAMS

        12       7900 JAMES

        13       7902 FORD

        14       7934 MILLER



14 rows selected.



scott@oracle> select rn, empno, ename from

  2  ( select empno, ename, rownum rn from emp )

  3  where mod(rn,2)=1;



        RN      EMPNO ENAME

---------- ---------- ----------

         1       7369 SMITH

         3       7521 WARD

         5       7654 MARTIN

         7       7782 CLARK

         9       7839 KING

        11       7876 ADAMS

        13       7902 FORD



7 rows selected.

Ales

Cool!!! Excellent Ales,

This is right solution, Yes it works. The report req. was really different to print odd records.

Appreciate for this help.

Thanks
aph.

You will get a different result set at different time when you use rownum in a SQL statement.

tamilselvan:

You mean depending on INSERT or DELETE

Base on Ales solution if we insert one new row,
So what will be the result? Please see below and correct me. Thanks


scott@oracle> select rownum rn, empno, ename from emp;

RN EMPNO ENAME
---------- ---------- ----------
1 7369 SMITH
2 7499 ALLEN
3 7521 WARD
4 7566 JONES
5 7654 MARTIN
6 7698 BLAKE
7 7782 CLARK
8 7788 SCOTT
9 7839 KING
10 7844 TURNER
11 7876 ADAMS
12 7900 JAMES
13 7902 FORD
14 7934 MILLER
15 6666 XYZ (new row)

15 rows selected.


scott@oracle> select rn, empno, ename from
2 ( select empno, ename, rownum rn from emp )
3 where mod(rn,2)=1;

RN EMPNO ENAME
---------- ---------- ----------
1 7369 SMITH
3 7521 WARD
5 7654 MARTIN
7 7782 CLARK
9 7839 KING
11 7876 ADAMS
13 7902 FORD
15 6666 XYZ (new row)
8 rows selected.

No matter what solution you use, the results may vary from what is expected. The questions is: What is expected? 'Odd rows' in relation to what. One would assume that there should be an ORDER BY here. Otherwise, the order of the rows returned from the query will be completely arbitrary and cannot be guaranteed to be the same from run to run. You will first need to determine what the ORDER BY should be. Then, then ROWNUM solution will work fine.

- Chris

We are not using ORDER BY in this tmp table.

That is my point!

The phrase 'every other row' means *nothing* if it is not accompanied by 'with respect to a given order'

That is like asking for every third person out of a crowd. If I do not order the people in some repeatable fashion, then I will never be able to re-create the set. If I do it once, I get one set. I do it again, I get a completely different set. In essence, you are asking for 1/3 of the crowd, which is different than every third person. Every third person *assumes* that they are in a line. In database terms, this means they are ordered.

If you want 1/2 of the records, then:
SELECT * from table 1 where rownum <
(select count(*) * .5 from table1) will do just fine.

If you actually want 'every other record', then you *need* an ORDER BY for the request to make logical sense.

Now, this is not to say that you cannot write and run the query exactly as ALES wrote it. You can. You simply run the risk of getting an entirely different result set every time you run it. I can probably do a dozen things to the table that will not touch the data, yet will change the order in which the data will be retrieved from this query. So, as I said, you will effectively be retrieving 1/2 the records, as opposed to 'every other' record. There is a logical difference.

Finally, to cover all the bases, even if the records were inserted into the table using a given order, that does not count. How the data was inserted is also completely irrelevant to how it will come out. If you want the data to come out in a given order, you must include the ORDER BY.

Make sense?

- Chris



[Edited by chrisrlong on 08-30-2001 at 10:50 AM]

Thanks of your explanation. The temp table already in ordered and the information is stored like one row credit and 2nd row is debit and so on. So what I was told to generate report of all credit lines.

In our case, temp table always contains first line of credit info.

Thanks
aph

Quote:

---

Originally posted by chrisrlong

Finally, to cover all the bases, even if the records were inserted into the table using a given order, that does not count. How the data was inserted is also completely irrelevant to how it will come out. If you want the data to come out in a given order, you must include the ORDER BY.

---

I cannot say it any more plainly. If you want the data to come out of a table in a specific order, then you *must* have an ORDER BY. Period. End of story. While it may have been working thus far, I repeat that there are at least a dozen reasons why the data might not come out of the table in the same order it went in.

Now, to step back from the question at hand and focusing on the problem at hand. If you need to distinguish between Credit and Debit records, why in the world don't you have a TransactionType_Cd field, specifying whether the transaction was a credit or debit?? The solution you are asking for is a hack at best. If this will be used for some once-and-done query, that's one thing, but I absolutely would not use this type of hack in a production environment.

Just my .02,

- Chris

Ok, This was the temporary report as you said once-and-done
Yes! Here we had this type of situation raised and I have done with ALES solution.

But on the other side what could be the best solution you can provide us for this type of ODD rows.
I would really appreciate if you can help.

Thanks

The problem with rownum is it is not consistent.
For example, a table has data in 10 blocks. Assume all the blocks are full.
Now you delete 2 rows, one in block 3 and another in block 8. Assume both blocks 3 and 8 are availble in the free list. And Later, you insert only one row. Now you do n't know where the row is stored. It could be in block 3 or block 8.
And after this first insert, one more row is inserted.

Now if you use rownum in your SQL statement, you will see a different result set.

The point is use rownum carefully.

Tamilselvan, I think you are thinking of ROWID, not ROWNUM. IMHO, ROWID should *never* appear in production SQL.

As for ROWNUM, it is perfectly acceptable as long as the results are ordered. Without an ORDER BY, as I said, there are probably a dozen different ways to change the results of the query *without* even changing the data. The ORDER BY is required, and would eliminate your concerns as well.

Now, back to the latest issue. Again, the problem, as I said, is in the solution. The solution is *not* to retrieve every odd record from the table. The solution is to add a TransactionType_Cd field that specifies whether the record is a debit or a credit and select based on that.

Asking for every odd row is a hack! Plain and simple.

Having said that, if one were truly in a bad situation and *had no other alternative*, the proper way to retrieve every other row, as I said, is to use the ROWNUM solution *with* an ORDER BY. You can *never* guarantee the order of the data coming out of the table *without* an ORDER BY. I'm sorry, but that's the way it is.

- Chris

Our discussion is going very long and also positive regarding ODD ROW issue.

One thing I would like to know for every other (ODD row) and based on the ALES solution what is the use of ORDER BY. As I have mentioned several time that table itself contained sorted info and all I have to print
1st
3rd
5th
7th
9th
.
.
.
.

Again what will be the effect of ORDER BY if we consider ALES solution.

Thanks
aph

Quote:

---

Originally posted by aph
table itself contained sorted info

---

Look, aph, I really don't know how many ways I can say the same thing, but I will try one last time...

In *any* query, you *cannot guarantee* the order of the rows *without* an ORDER BY. I don't care how you put the data *into* the table. That is *completely* irrelevant. This is *not* a flat file. The records are *NOT* guaranteed to be stored physically inside the table in the same order as they were inserted. Now, if you are inserting into a brand new table, and you have lots of contiguous free space, and you are the only user on the system, then you have minimized many of the variables that would change the data, *BUT* you have not eliminated them all. Granted, when you do a SELECT, the rows *may* come out in the order they were inserted. Great! I'm happy for you! But that *STILL* does not *guarantee* that every single time you run that query, the data will come out in the same order. As I keep telling you, there are many events that can change the order. There are also many variables that can effect how the data was inserted into the table. There are also many variables that can affect how the data is retrieved from the table. All of these can change the order of the rows as retrieved from your SELECT statement. *The only way* to eliminate *all* these variables is to add an ORDER BY to the SELECT.

That's the best I can do. If you still don't believe me, I'm sorry.

Here is the query

Code:

---

SELECT
  RN,
  EMPNO,
  ENAME
FROM
  (
      SELECT
        EMPNO,
        ENAME,
        ROWNUM RN
      FROM
        EMP
      ORDER BY
        EMPNO
  )
WHERE
  MOD(RN,2)=1;

---

You will need enough columns in the ORDER BY to guarantee the order of the rows.

It just occurred to me that you may have been confused about where the ORDER BY went. It goes with the INSIDE query. This is because, as I said, you need to order the rows *before* asking for the 'odd' rows. This is the entire point of the ORDER BY - to *guarantee* which rows are the odd vs the even rows. Otherwise, there is no way to *guarantee* that you will not get 2 debits as the first 2 rows. You first need to order the results as D)ebit/Ci)redt/D/C/D/C/D/C *before* you can get rid of the even rows and only be left with the Debits.

Finally, I can't help myself from again stating that this is a very nasty solution for the problem. There almost *has* to be some column that distinguishes the Credits from the Debits. The proper solution would be to select the rows WHERE TransactionType_Cd = 'D'

I hope this all made sense,

- Chris

Dear,
I agree to chris, the best thing to do is add a column of TranType based on whichu can get the desired results without much pain and not lot of time will get saved. :)

CHRIS:
I got your point regarding ORDER BY. And I am 100% agree with your point. I am really sorry that I could understand your technical point little late but infect it was really deep and dark for me that why I have to use ORDER BY. Actually your last reply help me a lot because of you query with illustration & demonstration. This is what I was expecting from you to show me technically not by text writing.

I appreciate for this help. I totally understand you have spend a lot of time to help me resolve this critical issue. I am going to change my query and will see the results in report.

ALES where are you? I thankful to your initial solution.
CHRIS I am very glad about your time and technical material, which you spend and deliver a solution.
VIJAY I also like your idea but some conditions apply and I can not add new column it is not in my hand.

Once again thanks
aph.

Hi,
nice to read I helped you, aph.
To be honest, I posted my solution although I couldn't see any practical purpose of it.
If I knew what your real problem was I'd suggest a different solution.
This time you were lucky and this simple one was good enough ;-)).

Hat off to you, Chris.
Excellent knowledge, great patience, clear language. These are things I like this forum for.

Have a nice time!

Ales