Hacker News Reader

Comment by avmich | Hacker News Reader

I recently was wondering how to write a program, in SQL, which generates consecutive integers - 0, 1, 2, 3... - up to an arbitrary input value. Only standard SQL is allowed, and familiar constructs are preferred - the code should be understandable to the maintainer.

In J it's i. <n>, like this - i. 5 produces 0 1 2 3 4 .

View on HN

SQL is not turing complete, at least not without recursions. But you will still get very far when it comes to wrangling data. So far that you may not even need another tool for that purpose.

What do you want to use that list for?

I think their point was that J and SQL target different domains, and each will be stronger in its respective domain. If I want to join two tables, filtering on some value, sorting, and viewing the first ten results, SQL will likely be the cleaner syntax. If I want to apply a polynomial function to a list of values, and calculate the standard deviation of the result, J will be much cleaner.

There's some similarity, at a high level, with how they work. But it doesn't really make sense to say one is, overall, better than the other, because they don't solve the same problem. It's like saying a hammer is better than a screwdriver.

  WITH RECURSIVE num AS (
      SELECT 1 AS id
      UNION ALL
      SELECT id + 1 FROM num
  )
  SELECT * FROM num LIMIT 5;

SQL:1999 IIRC.

Admittedly though, recursive CTE's like that are a bit of a minefield in practice. It's easy to confuse the query optimizer after chaining a few of those together, or to outright hit a recursion limit (32,767 in SQL Server).

Reply on news.ycombinator.com