CODEWITHSUNDEEP
sqloracle-databasesyntax
Frank Glover
Frank Glover

Reputation: 1

Select by row in Oracle

I know its non conventional, but I have a need to select data by row number (as this is how an error report gives us feedback)

Here is the SQL I came up with but it doesn't work. I did start with a MYSQL query, that did work and passed it through a converter as I don't know that much about Oracle's syntax.

SELECT * 

FROM (
    SELECT ACCOUNTID, ACCOUNT_SSN, AGENTCODEID, @curRow := FROM dual @curRow + 1 AS row_number
FROM 
    agent_code_details_stage_0808    
    JOIN (
        SELECT <<@curRow>> = 0
    ) r
) sub

WHERE sub.row_number = 78 or 
sub.row_number = 277

Update: When we open the table, there is no "order" specified, but the system shows row numbers. I believe we are using SQL Developer. Ill check again to see if there is a default column it's sorting by.

Is there a way to write this without requiring an order? Most databases are inherently sequential as new data is written to it.. Therefore, I need to pull by row as it was created ( there is no data in the table for me to compare to or sort by ) Think of this as a log file, more than a database. I get an email telling me there was a row in the data that failed, and I need to be able to get to that exact row as it falls in the DB to figure out what failed.

Upvotes: 0

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Oracle has row_number():

SELECT acd.*
FROM (SELECT acd.*,
             ROW_NUMBER() OVER (ORDER BY ?) as seqnum
      FROM agent_code_details_stage_0808 acd 
    ) acd
WHERE sub.row_number IN (78, 277);

The one caveat is that SQL tables represent unordered sets. There is no such thing as row 78 in a SQL table in general. There is only row 78 when ordered by something. The ? is for the column that specifies the ordering.

Upvotes: 2

Related Questions