View previous topic :: View next topic
|
Author |
Message |
NikhilMF10
New User
Joined: 19 Nov 2008 Posts: 8 Location: Pune
|
|
|
|
Requirement :
I have a table1 with column A and B, this table has 25 rows. Now I want to sort this table based on column B and fetch only the first 5 rows from it. I do not want to go for a cursor but want to do it in single insert statement. I tried using the query below. But it says INVALID KEYWORD "ORDER"
My query is
INSERT INTO TABLE1
SELECT *
FROM TABLE1
ORDER BY B
FETCH FIRST 5 ROWS ONLY |
|
Back to top |
|
|
Cristopher
New User
Joined: 31 Jul 2008 Posts: 53 Location: NY
|
|
|
|
Hi NikhilMF10, may be I am missing out on the actual requirement.
Why do you wish to insert data from the same table from which you are fetching it?
The order/sorting comes into picture when you are selecting the data from the table and not while inserting.
Cris |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
if there is a unique index, possible dup entries will prohibit the update.
Basic syntax is that you can not put an ORDER BY on the Select sub-clause of an INSERT. |
|
Back to top |
|
|
NikhilMF10
New User
Joined: 19 Nov 2008 Posts: 8 Location: Pune
|
|
|
|
Hi,
I even tried using another Table table2 in first line, but this doesn’t work either
Please let me know the solution. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
dbzTHEdinosauer wrote: |
Basic syntax is that you can not put an ORDER BY on the Select sub-clause of an INSERT. |
Nikhil,
Guess you missed the above one. It is not the order of tables causing the problem. Please explain what you are trying to achieve. |
|
Back to top |
|
|
NikhilMF10
New User
Joined: 19 Nov 2008 Posts: 8 Location: Pune
|
|
|
|
I actually want, of the total 25 rows in the table, i want to select only 5 rows based on column B in ascending order- delete the rest.
Example :
Table1 has 10 rows
A B
pady 10
dar 1
parth 9
scot 6
ham 4
tirk 8
scob 2
dan 13
Andy 16
San 11
Now what i want is as below the best 5(as in ascending)
A B
dar 1
scob 2
ham 4
scot 6
tirk 8
irrespective of i have it in same table or the other, since i have to use the above table of five to sellect a vaue from it randomly using a random function. |
|
Back to top |
|
|
NikhilMF10
New User
Joined: 19 Nov 2008 Posts: 8 Location: Pune
|
|
|
|
PLease consider A as column 1 and B as column 2. |
|
Back to top |
|
|
ashok_uddaraju
New User
Joined: 21 Feb 2007 Posts: 72 Location: US
|
|
|
|
i have tried on this query and it worked for fetching the first 5 rows
SELECT *
FROM TABLE1
ORDER BY B
FETCH FIRST 5 ROWS ONLY
where B should be either the column name or column number. |
|
Back to top |
|
|
NikhilMF10
New User
Joined: 19 Nov 2008 Posts: 8 Location: Pune
|
|
|
|
Yes it works for sellecting, but it doesnt work for inserting.
I can insert using a cursor but i do not want to because of performance reasons.
I want to do it in one single insert statement. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Quote: |
irrespective of i have it in same table or the other, since i have to use the above table of five to sellect a vaue from it randomly using a random function. |
What did you mean by the above statement. It's still not clear what you're trying. insert/select/delete. You have mentioned all . Are you just trying to filter out 5 rows from 25 rows and display in output, or do you want to physically delete some rows from the table? |
|
Back to top |
|
|
NikhilMF10
New User
Joined: 19 Nov 2008 Posts: 8 Location: Pune
|
|
|
|
Yes i want to filter out 5 rows only in a new table or the same table can be used as i do not need the rest of the rows. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
You need to focus on getting the result you need. . .
Quote: |
I can insert using a cursor but i do not want to because of performance reasons. |
Have you shown some major performance issue with this exact requirement? Forget the rumor that all cursors are bad. . .
I suggest that if you want to read the first 5 rows of the result and inisert them elsewhere using a cursor will make exactly no (or almost immeasurable) difference in performance. |
|
Back to top |
|
|
NikhilMF10
New User
Joined: 19 Nov 2008 Posts: 8 Location: Pune
|
|
|
|
5 rows is just an example.
i have to deal with thousands of rows.
please suggest any way of doing it using a single insert query. |
|
Back to top |
|
|
darkstar13
New User
Joined: 06 Nov 2008 Posts: 46 Location: Manila, Philippines
|
|
|
|
Hi Nik,
Your example should be representative of what you REALLY want to achieve.
If you have to insert thousands of rows, (even just 5), I don't think you can do that in one select-insert statement. Using a cursor to select each row and then inserting them is the usual way to go. I don't see anything wrong with doing that.
However, if you want to do MASS INSERT, you can do it with just a simple
Code: |
INSERT INTO TABLE
SELECT *
FROM TABLE
FETCH FIRST 5 ROWS ONLY |
You don't need the ORDER BY. Why? We insert data in the table to populate them. When we want to get the records in the ORDER, then we issue the ORDER BY when we do the select. |
|
Back to top |
|
|
|