On 12/02/09, Rory Campbell-Lange (rory@campbell-lange.net) wrote:
> I realise that for every row in my users table (which has a unique
> integer field) I can update it if I construct a matching id field
> against a random row from the testnames table.
I can make my join table pretty well by using the ranking procedures
outlined here: http://www.barik.net/archive/2006/04/30/162447/
CREATE TEMPORARY SEQUENCE rank_seq;
select nextval('rank_seq') AS id, firstname, lastname from testnames;
or
SELECT
firstname, lastname,
(SELECT
count(*)
FROM
testnames t2
WHERE
t2.firstname < t1.firstname) + 2 AS id
FROM
testnames t1
ORDER BY
id;
The second method skips some ids (probably because I haven't got an
integer column in testnames)? It looks like I will have to go for the
first procedure or write a function with a loop and counter.
Any other ideas?
Rory