SQL query How to select nth row from a table if they are not in any order

5 pts.
Tags:
SQL
SQL data
SQL Query
SQL tables
How to select nth row from a table if they are not in any order

Answer Wiki

Thanks. We'll let you know when a new response is added.

Thank you for visiting ITKE.

We are happy to help you with solving specific IT questions, but need as
much information as possible to do so. Let us know about the problem you
are trying to solve, how you are approaching it and what work you’ve
done so far, and we can help guide you in the right direction.

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    To talk about 'nth' position, there must be an order.
    69,920 pointsBadges:
    report
  • TomLiotta
    What DBMS are you using? (DB2? MS SQL Server? MySQL? Other?) Under some of them, you can access by physical row number. Tom
    125,585 pointsBadges:
    report
  • carlosdl
    "Under some of them, you can access by physical row number" And that involves an order as well...
    69,920 pointsBadges:
    report
  • TomLiotta
    And that involves an order as well… As long as that means "a physical order of how rows are stored on disk", then I suppose that that's true. If it relates to a specification such as ORDER BY as understood within SQL, not so. An example that I just ran under DB2 to select only physical record #1:
    SELECT rrn(a) FROM accp a WHERE rrn(a)=1
    No ORDER BY specified on the statement. If I reorganize the physical and cause the rows to occupy different physical disk positions, I will get the new row that comes first on disk. I suppose a clear concept of "order" would need to be supplied. The OP probably needs to be more clear. Row number in a result set and physical record number are usually different values. Tom
    125,585 pointsBadges:
    report
  • carlosdl
    Agreed. That's what I meant. When one talks about the nth position, an order is necessarily involved. It could be the order of how rows are stored on disk, the order of how they are displayed when no order is specified (which is not necessarily the same), or an order specified with an ORDER BY clause in a query. "If I reorganize the physical and cause the rows to occupy different physical disk positions, I will get the new row that comes first on disk." That's the problem. If no order is specified, there is no way to be completely sure that you will get the results in the same order every time you run the same query against the same data (some DBMSs might apply a 'default' order criteria when no order is specified (the pk, for example), but I wouldn't rely on that).
    69,920 pointsBadges:
    report
  • TomLiotta
    If no order is specified, there is no way to be completely sure that you will get the results in the same order every time you run the same query against the same data... Yes, very much agreed. But note that the order of records retrieved isn't important here. The result set will have no guaranteed order, but the set of rows will be the same. They might be ordered 1, 2, 3 one time and 3, 2, 1 the next time. But the same rows will be there -- unless the file is changed, by reorganization, by altered attributes or by UPDATE, INSERT or DELETE. Of course, when a file is changed, you should expect a different set of rows to be returned. However, the set will still be those defined by the WHERE clause. In the case of the RRN() function, they will be the records from the specified physical positions regardless of their position in the final result set... assuming that the DBMS has a RRN() or a similar function. Tom
    125,585 pointsBadges:
    report
  • carlosdl
    "But note that the order of records retrieved isn’t important here" That's true, mainly because the OP was asking about a query that would return just one row, the nth one. If the RRN() function (or some other equivalent) uses the physical order on disk, but some operations could change that physical order, using it to get the nth record could be risky (IMO) , but it could be the only option if there is really no other order as the OP said. Interesting discussion. Unfortunately, the OP wasn't involved.
    69,920 pointsBadges:
    report
  • Kccrosser
    Keep in mind also that database engine updates can result in changes to the default result list order when no Order By clause is specified. Also, when the distribution of key values in the table changes, this can cause the result set to return in a different order depending on how the query optimizer decides to execute the query. So - the short answer is "If there is no order specified in a query, the 'nth' position record is only meaningful for the current result set of the query - rerunning the same query again may result in a different record occupying the same 'nth' position." If the OP was trying to implement some record set "browser" that could navigate forwards and backwards through the current unordered static set of records returned from a query, I would just write a set of stored procedures that read the data into a static temporary table and assigned sequential row number values, then provide methods to retrieve the data from that static table by the assigned row numbers.
    3,830 pointsBadges:
    report
  • Chippy088
    Maybe the question means that the command syntax is required to display row n in a sql database. It's homework. (Doesn't the short format of the question ring bells?) I believe SQL is implicit in the question heading. If selecting/displaying the Nth row is required, then there must be an order, otherwise the Nth position would be meaningless. Without a more precise definition of the problem, there is no point in trying to guess the answer. We are after all said and done IT professionals and not mind readers.
    4,625 pointsBadges:
    report
  • TomLiotta
    ...the ‘nth’ position record is only meaningful for the current result set of the query... Sorry, but this isn't true for the RRN() function. RRN(1) is not equivalent to the first position in the result set and RRN(n) is not equivalent to the nth position in the result set. The RRN() function refers to physical order of the underlying table as it appears on disk. This is very different from the 'result set' which doesn't exist at all until the statement executes. A RRN() value will most likely be different from a ROW_NUMBER value for the same row -- ROW_NUMBER is a reference to position in the result set. The two values may be in exactly opposite order if ORDER BY is descending over RRN(). A physical 'record' is not the same as a 'result set row'. Physical records are read from disk in order to generate result set rows. Result sets are logical rather than physical. The existence of a function such as RRN() is dependent on the particular DBMS. I don't think that MS SQL Server has a similar function, but SQL for DB2 on System i does. Oracle... I don't know. In any case, it depends on what the OP means by "order" and "nth row" as well as the platform that's available to the OP. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following