Can any one tell me how to generate number sequence by just a select query with dual table and without creating a Sequence??????

can anybody tell me how to generate sequence in netezza server..??

Yes, and with a standard sql that works on any server.

The method is to use a self-join with grouping to generate the sequence number.

SELECT t1.userid, COUNT(t1.tableid) AS sequence, t1.tableid, t1.tableid > = t2.tableid AS flg
FROM table t1 INNER JOIN table t2 ON t1.userid = t2.userid
GROUP BY t1.userid, t1.tableid, flg
HAVING flg = TRUE

What this does is to join each row to itself and all those within the same sub-set, in this case ‘userid’, that were inserted before it and then count them. So the first entry for each user is only joined to itself = 1, the second is joined to itself and the first = 2, third to itself and the first two = 3, etc.

To reverse the sequence simply reverse the selection

t1.tableid < = t2.tableid AS flg

This example uses the userid as the sub-set definition, but it could be any field you want.

You can find a fuller discussion of this solution with examples of its' use at mu blog davidsoussan.co.uk

i have a column containing 4 digit numbers ...such as 8841,8853,8823,9951,8841..etc column contain repeat values too ...
i need to convert them into 7 digit...and want to assign them 3 more digits
eg
8841 gets converted into 8841001 next
8841 gets converted into 8841002 next
8841 gets converted into 8841003 next
8853 gets converted into 8853001 next
9951 gets converted into 9951001 next
9951 gets converted into 9951002 and so on...
kindly help....i am a noob in sql...plz

Nice post Lepa......................

Regards,

Paritosh

You could simplify the algorithm :

SQL> SELECT seq.NEXTVAL + floor((seq.NEXTVAL)/5) not_divisible_5 FROM dual;

it is more intuitive as the 'not required' factor becomes the denominator

select level
from dual
connect by level <= 10 ;

Anyone can help me how to add the sequence in the 1 query which the query have select and join other table, but for the sequent, it was the only one table. How to join it if there is no synonym table for sequent with the main table selected.