How do I insert a particular row within an existing table at a particular place between two rows?
Short answer: there is no "position" in a relational table, so you can't.
The long answer acknowledges that rows can be sorted in a SELECT result set by using the ORDER BY clause. Obviously, this requires that you designate at least one column in the ORDER BY clause for the database to sort on. Therefore if you wanted to insert a row into the table so that it is positioned in a particular place, this would make sense only if you provide a value for the particular column that you're going to sort on, such that this value lies between the values of the same column for the two rows that the new row must sort between whenever you run a query using the same ORDER BY clause. Whew!
I don't think you could automatically generate this "in between" value, either. You'd need to provide two values for whatever column you choose, in order to identify the two rows. Perhaps you're thinking of selecting the two rows based on some other column, for example, by providing values for column2 and then calculating an "in between" value for column1 like this query --
select avg(column1) from thetable where column2 in ('value2A', 'value2B')
This is extremely awkward, because you'd need to round the average to the nearest integer, and then you're still faced with a problem if the value you end up with for column1 already exists in the table!
So in the long run, deciding on an "in between" value for a particular column appears like it will have to be a manual process. To answer your question, though, yes, you can do it, but you need to decide which column you're dealing with, and you'll probably have to run a SELECT ... ORDER BY to determine the values that you want to insert between.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.