Forum OpenACS Development: How do I get first half rows from ORACLE table

Collapse
1: How do I get first half rows from ORACLE table
Posted by Yogesh Patil on 11/15/03 02:11 PM
Hello All,

  I want to take first half rows from one table and insert  them into another table.
  The structure of both tables is same.

  How can I do it ?

I have written a query like this

insert into table2(col1, col2, col3) values (select rownum<=(count(table1.col1)/2) from table1);

but it won't work

Please send me query as early as possible

Thank You in advance

Best Regards

Yogesh
Collapse
2: Re: How do I get first half rows from ORACLE table (response to 1)
Posted by Dirk Gomez on 11/16/03 12:11 AM
Why do you want to do this?
Collapse
3: Re: How do I get first half rows from ORACLE table (response to 1)
Posted by Dirk Gomez on 11/16/03 11:26 AM
If it's Oracle, you may want to look into the SAMPLE clause.
Collapse
4: Re: How do I get first half rows from ORACLE table (response to 1)
Posted by Sebastiano Pilla on 11/17/03 10:10 AM
"First half" according to what order? Unless you impose an ordering on the result set (with an "order by" clause) it's impossibile to talk about first or second, or last if that matters. Oracle manages to store data in such a way that there's no guarantee that a simple select without order by will return the data in the order that has been inserted.
Collapse
5: Re: How do I get first half rows from ORACLE table (response to 4)
Posted by Nis Jørgensen on 11/19/03 11:30 AM
So, is there any way to return a subset of the records, based on an explicit ordering? Something like mySQL's LIMIT. It seems like ROWNUM can not be used for this purpose (although I may be wrong).
Collapse
6: Re: How do I get first half rows from ORACLE table (response to 5)
Posted by Dirk Gomez on 11/19/03 11:34 AM
select * from
  (select a,b,c,rownum
  from foobar
  order by bla)
where rownum < 10

should do the trick.

Collapse
7: Re: How do I get first half rows from ORACLE table (response to 1)
Posted by Vamshi Krishna Kaniganti on 11/19/03 12:55 PM
insert into newly_acquired_stocks
        select * from my_stocks
        where rownum < = (select count(*)/2 from my_stocks);

this will work out check it out