On Tue, Sep 16, 2014 at 5:51 AM, Paul Sanderson
<sandersonforens...@gmail.com> wrote:
> I want to create a join on two tables and add a unique number to each
> returned row. Can this be done with a SQL query?
>
> Thanks
> --
> Paul
> www.sandersonforensics.com

Just a bit of thinking out loud, but I wonder if a RECURSIVE CTE could
be used to generate the number, somehow.

WITH RECURSIVE counter(x) AS )
   SELECT 1 AS x
   UNION ALL
   SELECT x+1 FROM counter)
SELECT x AS uniqueNo FROM counter
OUTER LEFT JOIN
( SELECT a.col1 AS col1 , b.col2 as col2 FROM table1 AS a
  JOIN
  table2 AS b
  ON a.col3 = b.col3) AS joinTable
ORDER BY col1;

That may well not work. I don't have anything around to try it on.
And, at 06:23 local time, with insufficient caffeine intake, that is
the best that I can do so far.

-- 
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to