Serial value in select
Q: how to produce a sequential row-number column (1,2,3...) alongside an ordered id column in IDS 10, when no such column exists in the table. Several workarounds were offered: create a temporary SEQUENCE and select seq.nextval with the query, then drop it (Carsten Haese); insert the rows into a temp table with a SERIAL column and select from that (Art Kagel); or use an SPL function holding a GLOBAL counter as a pseudo-rownum — though the author noted you can't filter on its result. Another poster suggested wrapping it in SPL or numbering rows in the client application. No single answer was confirmed by the original poster.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Verision: IDS 10 Consider following values in a table: id 1232 1231 1230 1233 Now I want to write a select that is order by id that will generate something like: serial | id 1 1230 2 1231 3 1232 4 1233 I am not sure about how to get serial value in select when it's not part of the table.
On Wed, 2008-03-12 at 17:04 -0700, Mohit wrote:
> Verision: IDS 10
>
> Consider following values in a table:
>
> id
> 1232
> 1231
> 1230
> 1233
>
> Now I want to write a select that is order by id that will generate
> something like:
>
> serial | id
> 1 1230
> 2 1231
> 3 1232
> 4 1233
>
>
> I am not sure about how to get serial value in select when it's not
> part of the table.
Here's one solution:
create sequence tmpseq;
select tmpseq.nextval, sometable.id
from sometable
order by sometable.id;drop sequence tmpseq;
HTH,
--
Carsten Haese
http://informixdb.sourceforge.net
Mohit wrote:
> Verision: IDS 10
>
> Consider following values in a table:
>
> id
> 1232
> 1231
> 1230
> 1233
>
> Now I want to write a select that is order by id that will generate
> something like:
>
> serial | id
> 1 1230
> 2 1231
> 3 1232
> 4 1233
>
>
> I am not sure about how to get serial value in select when it's not
> part of the table.
> __________________________________________
Another option to Carsten's elegant solution:
create temp table fred ( ser serial, id int ) with no log;
insert into fred
select 0, id from sometable;
select ser as serial, id from fred order by 1;
drop table fred;
Art S. Kagel
Oninit
===========================================================================================
Please access the attached hyperlink for an important electronic communications disclaimer:
http://www.oninit.com/home/disclaimer.php
===========================================================================================
Mohit wrote:
> Verision: IDS 10
>
> Consider following values in a table:
>
> id
> 1232
> 1231
> 1230
> 1233
>
> Now I want to write a select that is order by id that will generate
> something like:
>
> serial | id
> 1 1230
> 2 1231
> 3 1232
> 4 1233
>
>
> I am not sure about how to get serial value in select when it's not
> part of the table.
Just to add to the confusion:
--DROP PROCEDURE pseudo_rownum;
CREATE PROCEDURE pseudo_rownum(i INTEGER) RETURNING INT;
DEFINE GLOBAL a INTEGER DEFAULT 0;
IF i = 0 THEN
LET a=-1;
END IF
LET a = a+1;
RETURN a;
END PROCEDURE;
-- reset rownum...
EXECUTE PROCEDURE pseudo_rownum(0);
SELECT c.*, pseudo_rownum(-1)
FROM customer c;
You could even include conditions on the return value of pseudo_rownum...
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
Fernando Nunes wrote:
> Mohit wrote:
>> Verision: IDS 10
>>
>> Consider following values in a table:
>>
>> id
>> 1232
>> 1231
>> 1230
>> 1233
>>
>> Now I want to write a select that is order by id that will generate
>> something like:
>>
>> serial | id
>> 1 1230
>> 2 1231
>> 3 1232
>> 4 1233
>>
>>
>> I am not sure about how to get serial value in select when it's not
>> part of the table.
>
> Just to add to the confusion:
>
>
>
> --DROP PROCEDURE pseudo_rownum;
> CREATE PROCEDURE pseudo_rownum(i INTEGER) RETURNING INT;>
> DEFINE GLOBAL a INTEGER DEFAULT 0;
> IF i = 0 THEN
> LET a=-1;
> END IF
>
> LET a = a+1;
> RETURN a;
> END PROCEDURE;
>
> -- reset rownum...
> EXECUTE PROCEDURE pseudo_rownum(0);>
>
> SELECT c.*, pseudo_rownum(-1)
> FROM customer c;
>
> You could even include conditions on the return value of pseudo_rownum...
No... you can use it, but you can't include conditions against it...
Interesting stuff...
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
On Mar 13, 12:04 am, Mohit <mohitanch...@gmail.com> wrote: > Verision: IDS 10 > > Consider following values in a table: > > id > 1232 > 1231 > 1230 > 1233 > > Now I want to write a select that is order by id that will generate > something like: > > serial | id > 1 1230 > 2 1231 > 3 1232 > 4 1233 > > I am not sure about how to get serial value in select when it's not > part of the table. If you wrap the select within an SPL routine then this becomes pretty trivial, if you want to write straight SQL then I can think of a nasty approach using a self-join...but I don't think it's worth posting it here. Can you apply the 'relative row number' in the consuming application?