How do I retrieve a partuclar row from a table? For example, I want to retrieve only the second row inserted into the destination table. Is it possible through the use of row IDs?
Your answer all depends on what you mean as the "second row" of the table. Let's assume I insert four rows into my table as denoted by:
1 2 3 4I then delete row "2". The table rows will look like:
1 3 4Which is the second row here? Is it the NULL row or the "3"?
Now assume that I insert the value "5". The table rows might look like:
1 5 3 4What is the second row here? Is it the "5", or is it the "3" which is the second "oldest" row in the table?
If you are looking for the second row, as stored in physical sort order, then a query similar to the following might help you out:
SELECT x.ename FROM (SELECT ename,rownum AS rowno FROM emp) x WHERE x.rowno=2;If you are looking for the second oldest row, then you will need to come up with some other way to sort your data by time and bring out just the second oldest row.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or 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 Oracle and 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 database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.