MySQL Subqueries

For most of my software development career, I used an Oracle database. At my current job, we use a MySQL database. So I am still learning the ins and outs of MySQL. Here is a common thing I would do in Oracle:

SELECT
  name, total_points
FROM (
  SELECT
    name,
    SUM(points) AS total_points
  FROM
    scores
  GROUP BY
    name
)
WHERE
  total_points >  10
ORDER BY
  total_points DESC, name

That would give me a list of all players who have scored more than 10 points. When I tried this same format in MySQL, I got this error:

Error Code: 1248. Every derived table must have its own alias

After a little searching, I came up with the solution. I had to name my inner query. So with a slight modification it worked:

SELECT
  tp.name, tp.total_points
FROM (
  SELECT
    name,
    SUM(points) AS total_points
  FROM
    scores
  GROUP BY
    name
) tp
WHERE
  total_points >  10
ORDER BY
  total_points DESC, name

Oracle Equivalent of MySQL's LIMIT (Or how to page results in Oracle)

I'm really just posting this for my own benefit but if you're reading this and it has been helpful, drop me a comment to let me know.

SELECT
  *
FROM (
  SELECT 
    a.*,
    ROW_NUMBER() OVER (ORDER BY column_to_sort) rn
  FROM
    my_table a
  WHERE
    my_column_to_filter = 'my_value_to_filter'
 )
WHERE
  rn >= my_starting_row AND rn <= my_ending_row

Evaluating Backreferences in Oracle REGEXP_REPLACE

Here's the problem. I want to use Oracle's REGEXP_REPLACE function to replace a string of digits with a zero-padded string. In Perl I would do it like this:

#--- Change xyz123 to xyz000123
my $string = "xyz123";
$string =~ s/(\d+)$/sprintf("%06d", $1)/e;

So here is what I started with in Oracle PL/SQL:

my_string := 'xyz123'
regexp_replace(my_string, '(\d+)$', lpad('\1', 6, 0));

And what I got was xyz0000123. What I discovered was that the LPAD function was being evaluated before the backreference (\1) was. So no matter what I did, the LPAD function always saw the string to be padded as 2 characters long.

So to get around this, I wrote another function called evalpad. With this, I do the replacement in 2 steps. The first is to replace the string with an embedded pad psuedo-function. Then I call a second function to do the actual replacement. Here's the code:

create or replace function evalpad (
  strIn in varchar
)
return
  varchar
as
  retval varchar(255);
  arg1 varchar(50);
  arg2 varchar(50);
  padded_number varchar(100);
begin
  retval := strIn;
  while (regexp_instr(retval, 'pad\((\d+)') > 0) loop
    arg1 := regexp_substr(retval, 'pad\((\d+)');
    arg1 := regexp_replace(arg1, 'pad\(', '');
    arg2 := regexp_substr(retval, 'pad\((\d+),\s*(\d+)');
    arg2 := regexp_replace(arg2, 'pad\((\d+),\s*', '');
    padded_number := lpad(arg1, arg2, 0);
    retval := regexp_replace(retval, 'pad\((\d+),\s*(\d+)\)', padded_number, 1, 1);
  end loop;
  return retval;
end;

Then in my original function I do this:

my_string := 'xyz123'
regexp_replace(my_string, '(\d+)$', 'pad(\1, 6)');
my_string := evalpad(my_string);