Assigning a Rank to table rows
With Informix Standard Engine, it was possible to used rowid and temp
tables to assign a rank value to rows on a table based on a specified
order.
We could previously achieve this by;
select name, code, volume
from table1
order by 3 desc
into temp tempa;
select *
from tempa
where rowid >=10
order by rowid;
We are now using Informix Online, which does not allow this due to
rowid no longer being accessible.
Does anyone know a method of assigning a rank value to rows in Informix
Online?
Thanks in advance
James Wilson
Sent via Deja.com http://www.deja.com/
Before you buy.
Hi!
I had to do this with stored procedure and fetch first N records.
Michael
JamesW wrote:
> With Informix Standard Engine, it was possible to used rowid and temp
> tables to assign a rank value to rows on a table based on a specified
> order.
>
> We could previously achieve this by;
>
> select name, code, volume
> from table1
> order by 3 desc
> into temp tempa;>
> select *
> from tempa
> where rowid >=10
> order by rowid;>
> We are now using Informix Online, which does not allow this due to
> rowid no longer being accessible.
>
> Does anyone know a method of assigning a rank value to rows in Informix
> Online?
>
> Thanks in advance
>
> James Wilson
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
SELECT FIRST x col1,col2 etc
FROM mytable
where ...
order by col1 desc
will give you the top x rows, you could use this as a sub query with NOT IN
to get everything else
E.g.
select name, code, volume
from table1
where name not in (select first 9 name
from table1
order by 3 desc
)
order by 3 desc
I'm sure this could be improved if you think about it for a bit :o)
--
---------------------------------------
Tony Flaherty aef@mfs.misys.co.uk
Analyst Programmer
Misys Financial Systems
All statements and opinions are my own,
Misys don't pay me enough to have opinions
on their behalf
.
JamesW wrote in message <8aj1fn$tfp$1@nnrp1.deja.com>...
>With Informix Standard Engine, it was possible to used rowid and temp
>tables to assign a rank value to rows on a table based on a specified
>order.
>
>We could previously achieve this by;
>
>select name, code, volume
>from table1
>order by 3 desc
>into temp tempa;>
>select *
>from tempa
>where rowid >=10
>order by rowid;>
>We are now using Informix Online, which does not allow this due to
>rowid no longer being accessible.
>
>Does anyone know a method of assigning a rank value to rows in Informix
>Online?
>
>Thanks in advance
>
>James Wilson
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Here's a painfully, clunky method that uses the SERIAL property while
getting around the "insert into...order by" and "select first... in a
sub-query" limitations by using 2 temp tables.
create temp table t3 (
l_serial serial,
my_col1 int,
my_col2 char(20)
) with no log;
select 0 l_serial, my_col1, my_col2 from my_table
order by 2 desc, 3 desc
into temp t2 with no log;
insert into t3
select * from t2;
select * from t3
where l_serial >=10;
BTW, Online does assign rowids, although they no longer start from 1 or
are sequential. Maybe you could find a pattern (and let us all know).
Rudy
JamesW wrote:
> With Informix Standard Engine, it was possible to used rowid and temp
> tables to assign a rank value to rows on a table based on a specified
> order.
>
> We could previously achieve this by;
>
> select name, code, volume
> from table1
> order by 3 desc
> into temp tempa;>
> select *
> from tempa
> where rowid >=10
> order by rowid;>
> We are now using Informix Online, which does not allow this due to
> rowid no longer being accessible.
>
> Does anyone know a method of assigning a rank value to rows in Informix
> Online?
>
> Thanks in advance
>
> James Wilson
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.