how to retrieve data by row number?

[alan123]

How can I write the clause to get records on specific row?
for example:
table ABC has:
firstname1 lastname1
firstname2 lastname2
firstname3 lastname3

Now I want to get the second line by sql statement.

Select * from ABC where ROWNUM=2?

thanks.

 

[sleipnir214]

Relational database tables do not have row numbers. Relational database tables are literally modeled after the mathematical concept of a set: a collection of items with no order. If the items are unordered, then none of them can be said to have a ordinal number.

That said, you can instruct a relational database to place an arbitrary order on the data returned in a result set. You do this with the "ORDER BY" clause of a "SELECT" query.

So if you want the specific second item of an ordered result set, something like:

SELECT * from tablename ORDER BY firstname, lastname LIMIT 2,1

The "LIMIT" clause in this case tells MySQL, starting at row 2, to return 1 record from the result set.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[BNPMike]

As sleipnir says, the relational model mandates that all data and relationships must be described by, be accessible via, the values of data items. Ordering or links are forbidden.

As we all know it works, and it works so well there are hardly any databases that don't now conform to this view of life.

As a result your query must be easily re-expressed in terms of some value rather than row number.