BlahStuff

A common need for web apps is to insert something into a database table and then immediately find out which ID got assigned to your new thing so that you can refer to it later in the script (probably while inserting more data).

A lot of folks start out doing something along the lines of:

INSERT INTO Addresses (address, city, state, postalcode) VALUES ('123 Main St.','Springfield','MA','01109');
SELECT max(id) as ID FROM Addresses;

After that, the folks on Microsoft SQL Server tend to discover the combination of SET NOCOUNT ON and @@IDENTITY:

SET NOCOUNT ON;
INSERT INTO Addresses (address, city, state, postalcode) VALUES (’123 Main St.’,'Springfield’,'MA’,'01109′);
SELECT id = @@IDENTITY;
SET NOCOUNT OFF;

Now, at my new gig, we use PostgreSQL. Today, I finally found myself wondering if/how I could achieve the NOCOUNT/@@IDENTITY behavior. Turns out, it’s super easy and almost even sexy (since version 8.2, I guess):

INSERT INTO Addresses (address, city, state, postalcode) VALUES ('123 Main St.','Springfield','MA','01109') RETURNING id;

That, my friends, is HOTT.

Sure, it’s non-standard SQL, but what’s the point of picking a particular RDMS if you don’t use the magic that it offers?

At my workplace, we often like to page through query results. That is, we’d like to show results in bunches of, say, 50.

Some databases (none that we use: PostgreSQL and MySQL) do this using LIMIT and OFFSET:
select * from table
where blah = 2
order by thing1, thing2
limit 50 offset 100
That query would give you the 3rd “page” of 50 results. (Note: I believe MySQL has shorthand: LIMIT 50,100)

I think Omnidex (a funky flat file indexing engine we use) lets us do something like:
select top 50 skip 100 * from table
where blah = 2
order by thing1, thing2

SQL server has the TOP, but I have no idea how to do offset/skip…

Anyway, what I do know is my boss kept bugging me to figure out how to do it in Oracle now that we are playing with a new database on that platform.

So, thanks to the internets, I present you with the wonderfully wacky world of paging in Oracle:

Select * from (
select t1.*, ROWNUM rn from (
select * from table where blah = 2 order by thing1, thing2
) t1
) where rn between 101 and 150

Key bits:

• You have to alias the inner-most select if you want to select * (That’s the “t1″ jazz.)
• ROWNUM is a calculated field. Each select has it’s own rownum column under the covers. This the real reason there are so many nested “views” to do this. The first select has rownum values that are calculated BEFORE the “order by”. The second select has rownum values that are calculated AFTER the “order by” in the inner-most select — this is the rownum set we actually want to filter, so we include it in the select (with an alias). The outer-most select is needed to actually apply that filter.

Caveat: My friendly neighborhood Oracle DBA tells me, “Rownum is virtual, you aren’t guaranteed that 101 through 150 are the same every time.”

Alternative: Said friendly neighborhood Oracle DBA came back with another method that I have yet to play with seems to work nicely:

Lets go this route, subtle difference, but there is a reason.

Select * from (
Select a.*, rownum rnum From (
select * from table where blah = 2 order by thing1, thing2
) a where rownum <= 150
) where rnum >= 101

It has to do with the way Oracle processes the COUNT(STOPKEY). Also I had to remember that in 8.1 that stuff wouldn’t work. With 9i and above we should be good to go.