Printable View
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
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.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.
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
Look, aph, I really don't know how many ways I can say the same thing, but I will try one last time...Quote:
Originally posted by aph
table itself contained sorted info
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
You will need enough columns in the ORDER BY to guarantee the order of the rows.Code:SELECT
RN,
EMPNO,
ENAME
FROM
(
SELECT
EMPNO,
ENAME,
ROWNUM RN
FROM
EMP
ORDER BY
EMPNO
)
WHERE
MOD(RN,2)=1;
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