This post originated from an RSS feed registered with Ruby Buzz
by Daniel Berger.
Original Post: SQL coup of the day
Feed Title: Testing 1,2,3...
Feed URL: http://djberg96.livejournal.com/data/rss
Feed Description: A blog on Ruby and other stuff.
I had an interesting challenge today. We have a table that keeps a boatload of log entries for certain orders. The sheer quantity of log entries was becoming problematic, so I was tasked with deleting any entries in this table in excess of 300, using Oracle PL/SQL. The catch, however, is that I had to keep the *first* 150 and the *last* 150 entries, by date.
Hmm...tricky.
I could have resorted to setting up a double loop, using a couple of counters to keep track of how many records I had iterated over.
Bah! A hack for mere mortals! If I can beat Master of Orion 2 at the "impossible" level with the Klackons (and I did), I can come up with a more elegant solution for this.
So, I learned about the various rank() functions. In my case, I used the row_number() function. And thus, the following SQL sprung (well, crept) from my mind:
select log_date, whatever,
row_number() over (order by log_date) date_rank_low,
row_number() over (order by log_date desc) date_rank_high
from some_log_table
where log_id = XXX
) ld
where ld.date_rank_low > 150 /* Oldest records */
and ld.date_rank_high > 150 /* Most recent records */
Previous Topic
