"Rownum" in Informix

[Kuldeep]

RowNum in Informix? How to implement it
any syntax,ponters,e.g

[Jonathan Stowe]

On Fri, 2006-04-14 at 07:44, Kuldeep wrote:
> RowNum in Informix? How to implement it
> any syntax,ponters,e.g
>

You mean like

select rowid from foo

?
--

This e-mail is sponsored by http://www.integration-house.com/

[Jonathan Leffler]

On 4/14/06, Jonathan Stowe <j...@gellyfish.com> wrote:
> On Fri, 2006-04-14 at 07:44, Kuldeep wrote:

> > RowNum in Informix? How to implement it
> > any syntax,ponters,e.g
>

> You mean like
>
> select rowid from foo

If you've got 10.00.UC4 (and not earlier versions, IIRC), check the
release notes.

[Serge Rielau]

Jonathan Stowe wrote:
> On Fri, 2006-04-14 at 07:44, Kuldeep wrote:
>> RowNum in Informix? How to implement it
>> any syntax,ponters,e.g
>>
>
> You mean like
>
> select rowid from foo
>
> ?

ROWNUM in Oracle is used to limit resultsets.
E.g.
SELECT * FROM T WHERE rownum < 5

So it's a "relative row number" rather than a rowid.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[da...@smooth1.co.uk]

"So it's a "relative row number" rather than a rowid. "

Select skip 10 first 5 a,b from tab1;

[keshav...@gmail.com]

Starting with version 10.00.xC3, IDS provides SKIP and enhanced FIRST
clause to provide
pagination. The main difference between using these and ROWNUM in
oracle is, IDS starts counting the rows after the ORDER BY clause is
evaluated where as Oracle's ROWNUMS is generated before the ORDER BY is
evaluated.

cheers,

Keshav.

Links to IDS manuals:

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls652.htm
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls656.htm

[Model-Bosch, Tilman]

Serge Rielau wrote:

> > Kuldeep wrote:
> >> RowNum in Informix? How to implement it
> >> any syntax,ponters,e.g

...

> >
> ROWNUM in Oracle is used to limit resultsets.
> E.g.
> SELECT * FROM T WHERE rownum < 5
>
> So it's a "relative row number" rather than a rowid.
>

The more or less equivalent in Informix is

SELECT FIRST n * from T

But be aware:

In ORACLE:
SELECT COUNT(*) FROM T WHERE ROWNUM <2

This will return '1' as the result. Oracle applies the 'rownum' to the
result
set and does then any aggregation (in this case counting)

In Informix:
SELECT FIRST 1 COUNT(*) from T

Will return one row, the result being nrows of the table T.
Informix gets the result set, then does all aggregation and only applies
the
'first n' to the final result that is being sent to the client.

HTH
Tilman

[Jean Sagi]

??

Do you mean some kind of rownum is implemented in IDS 10.00.UC4?

I checked UC4 release notes and found nothing about it (maybe my eyes ;) )

But last time I checked, this functionality is internal to IDS so you
can use limit/skip clauses.

But you actually cannot select an increasing number for every row that a
select returns.

I mean:

select some_kind_of_rownum, col1, col2, ...
from some_table
;

which yields to:

1, col1, col2, ...
2, col1, col2, ...
3, col1, col2, ...
4, col1, col2, ...

It's not supported yet by IDS...

or I am (Luckily) wrong?

J.

Jonathan Leffler escribió:

> On 4/14/06, Jonathan Stowe <j...@gellyfish.com> wrote:
>
>>On Fri, 2006-04-14 at 07:44, Kuldeep wrote:
>>

>>>RowNum in Informix? How to implement it
>>>any syntax,ponters,e.g
>>

>>You mean like
>>
>> select rowid from foo
>
>
>

> If you've got 10.00.UC4 (and not earlier versions, IIRC), check the
> release notes.

> _______________________________________________
> Informix-list mailing list
> Inform...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>