Asked  7 Months ago    Answers:  5   Viewed   50 times

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases:

  • SQL Server
  • MySQL
  • PostgreSQL
  • SQLite
  • Oracle

I am currently doing something like the following in SQL Server 2005, but I'd be interested in seeing other's more agnostic approaches:

WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate
FROM Orders)
FROM Ordered
WHERE RowNumber = 1000000

Credit for the above SQL: Firoz Ansari's Weblog

Update: See Troels Arvin's answer regarding the SQL standard. Troels, have you got any links we can cite?



There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it.

A really good site that talks about this and other things is

Basically, PostgreSQL and MySQL supports the non-standard:


Oracle, DB2 and MSSQL supports the standard windowing functions:

    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
  FROM tablename
) AS foo
WHERE rownumber <= n

(which I just copied from the site linked above since I never use those DBs)

Update: As of PostgreSQL 8.4 the standard windowing functions are supported, so expect the second example to work for PostgreSQL as well.

Update: SQLite added window functions support in version 3.25.0 on 2018-09-15 so both forms also work in SQLite.

Tuesday, June 1, 2021
answered 7 Months ago

sqlite3 cannot output binary data directly, so you have to convert the data to a hexdump, use cut to extract the hex digits from the blob literal, and use xxd (part of the vim package) to convert the hexdump back into binary:

sqlite3 my.db "SELECT quote(MyBlob) FROM MyTable WHERE id = 1;"  
| cut -d' -f2                                                   
| xxd -r -p                                                      
> object0.gz

With SQLite 3.8.6 or later, the command-line shell includes the fileio extension, which implements the writefile function:

sqlite3 my.db "SELECT writefile('object0.gz', MyBlob) FROM MyTable WHERE id = 1"
Sunday, August 1, 2021
answered 4 Months ago

using an auto increment field ... and i want to get the last one just added to join it with something else.

The key here is "just added". If you have a bunch of different users hit the db at the same time, I don't think you want user A to retrieve the record created by user B. That means you probably want to use the scope_identity() function to get that id rather than running a query on the table again right away.

Depending on the context you might also need @@identity (would include triggers) or ident_current('questions') (limited to a specific table, but not the specific scope). But scope_identity() is almost always the right one to use.

Here's an example:

DECLARE @NewOrderID int

INSERT INTO TABLE [Orders] (CustomerID) VALUES (1234)

SELECT @NewOrderID=scope_identity()

INSERT INTO TABLE [OrderLines] (OrderID, ProductID, Quantity) 
    SELECT @NewOrderID, ProductID, Quantity
    FROM [ShoppingCart]
    WHERE CustomerID=1234 AND SessionKey=4321

Based on the code you posted, you can do something like this:

// don't list the ID column: it should be an identity column that sql server will handle for you
const string QUERY = "INSERT INTO Questions (Question, Answer, CategoryID, Permission) " 
                   + "VALUES (@Question, @Answer, @CategoryID, @Permission);"
                   + "SELECT scope_identity();"; 

int NewQuestionID;
using (var cmd = new SqlCommand(QUERY, conn)) 
    cmd.Parameters.AddWithValue("@Question", question); 
    cmd.Parameters.AddWithValue("@Answer", answer); 
    cmd.Parameters.AddWithValue("@CategoryID", lastEdited);
    cmd.Parameters.AddWithValue("@Permission", categoryID);
    NewQuestionID = (int)cmd.ExecuteScalar(); 

See my answer to another question here:
get new SQL record ID

The problem now is that you'll likely want subsequent sql statements to be in the same transaction. You could do this with client code, but I find keeping it all on the server to be cleaner. You could do that by building a very long sql string, but I tend to prefer a stored procedure at this point.

I'm also not a fan of the .AddWithValue() method — I prefer explicitly defining the parameter types — but we can leave that for another day.

Finally, it's kind of late now, but I want to emphasize that it's really better to try to keep this all on the db. It's okay to run multiple statements in one sql command, and you want to reduce the number of round trips you need to make to the db and the amount of data you need to pass back and forth between the db and your app. It also makes it easier to get the transactions right and keep things atomic where they need to be.

Thursday, August 12, 2021
answered 4 Months ago

to_date() returns a date at 00:00:00, so you need to "remove" the minutes from the date you are comparing to:

select * 
from table
where trunc(es_date) = TO_DATE('27-APR-12','dd-MON-yy')

You probably want to create an index on trunc(es_date) if that is something you are doing on a regular basis.

The literal '27-APR-12' can fail very easily if the default date format is changed to anything different. So make sure you you always use to_date() with a proper format mask (or an ANSI literal: date '2012-04-27')

Although you did right in using to_date() and not relying on implict data type conversion, your usage of to_date() still has a subtle pitfall because of the format 'dd-MON-yy'.

With a different language setting this might easily fail e.g. TO_DATE('27-MAY-12','dd-MON-yy') when NLS_LANG is set to german. Avoid anything in the format that might be different in a different language. Using a four digit year and only numbers e.g. 'dd-mm-yyyy' or 'yyyy-mm-dd'

Thursday, September 9, 2021
answered 3 Months ago

Are you trying to build a full database search based on a key word? You can get table names , table column names and row counts by following code. But you cannot get row data within same code, you can get row data based on search results.

   --Set output size
   SET serveroutput ON size 1000000

      -- var table Name for cursor loop.
      t_c1_tname      user_tab_columns.table_name%TYPE;
      -- var column name for dynamic sql statement.
      t_c1_cname      user_tab_columns.column_name%TYPE;
      -- var string for dynamic sql statement.
      t_command       VARCHAR2(200);
      -- var for your search key word.
      l_str varchar2(20) := '%test%';
      -- var for dynamic cursor.
      t_cid           INTEGER;
      -- var for total row counts.
      t_total_records NUMBER(10);
      -- var for stat of executing dynamic sql statement.
      stat            INTEGER;
      --var for each loop row counts.
      row_count       INTEGER;
      -- var for minimum search result, here I set value = 0;
      t_limit         INTEGER := 0;    -- Only show tables with more rows

      -- cursor gets all table name, column name.
      CURSOR c1 IS select table_name, column_name
        from user_tab_columns
        where data_type in ( 'VARCHAR2' , 'VARCHAR', 'CHAR' );
      t_limit := 0;
      OPEN c1;
      FETCH c1 INTO t_c1_tname,t_c1_cname;
      -- Here create dynamic sql statement. 
      t_command := 'SELECT COUNT(0) FROM '||t_c1_tname || ' where ' || t_c1_cname ||' like '''|| l_str||'''';
      t_cid := DBMS_SQL.OPEN_CURSOR;
      -- Here execute dynamic sql statement. 
      stat := DBMS_SQL.EXECUTE(t_cid);
      row_count := DBMS_SQL.FETCH_ROWS(t_cid);
      -- Here get total row counts for each loop.
      IF t_total_records > t_limit THEN
         --Here output results
         DBMS_OUTPUT.PUT_LINE(RPAD(t_c1_tname,55,' ')||RPAD(t_c1_cname,55,' ')||
                        TO_CHAR(t_total_records,'99999999')||' record(s)');
         -- here you can insert results into your table.
         --INSERT INTO search_db_results VALUES (t_c1_tname,t_c1_cname,t_total_records);
      END IF;
      END LOOP;
      CLOSE c1;

     -- COMMIT if you have any insert statement.
     -- COMMIT;
Monday, November 29, 2021
answered 5 Days ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :