Postgres Pro
Downloads
Home   >   mailing lists

Thread: How do I select nth row from a table

How do I select nth row from a table

From
ganesanm
Date:
hi ,
I have a table (USR_GROUP_TBL) with  following attributes
  grp_name     char(30)  usr-name     char(15)

In my C program ( with embedded SQL using ecpg)
I need to select  the 'n'th user from the group.
Where n can any value from 1 to Max number of
users in the group.

The following query works well in non-embedded  sql
       select  user_name       from mail_file_dtl       where grp_name = 'grp_1'       order by user_name  limit 1
offset5;
 

Will return the 5th user in the group 'grp_1'.

How can I incorporate this query in  an  embedded sql ??

I tried  the following but ecpg returned error on the last
line.
exec sql       select  mfl_size into :mfl_size       from mail_file_dtl       where mfl_recipient = :mfl_recipient
order by mfl_file_name  limit 1 offset = :nNumber ;
 

Can a variable name be used in the offset option to pass
the value ?

I  encountered a very similar problem when I  tried to use
a cursor  with 'move forward' option.


Thanks
ganesh

Re: How do I select nth row from a table

From
Tom Lane
Date:
ganesanm <ganesanm@technauts.com> writes:
> How can I incorporate this query in  an  embedded sql ??

> I tried  the following but ecpg returned error on the last
> line.

>  exec sql
>         select  mfl_size into :mfl_size
>         from mail_file_dtl
>         where mfl_recipient = :mfl_recipient
>         order by mfl_file_name  limit 1 offset = :nNumber ;

> Can a variable name be used in the offset option to pass
> the value ?

It looks like not, at the moment.  I think the grammar for ecpg
needs to accept a "cvariable" in places where the underlying
backend grammar only accepts an ICONST.  It's probably too late
to fix this for 7.0, but it should be a todo item for 7.1...
        regards, tom lane

Re: How do I select nth row from a table

From
Michael Meskes
Date:
On Wed, Apr 19, 2000 at 05:18:01PM -0400, ganesanm wrote:
> The following query works well in non-embedded  sql
> 
>         select  user_name
>         from mail_file_dtl
>         where grp_name = 'grp_1'
>         order by user_name  limit 1 offset 5;

It should work as well under ECPG.

> I tried  the following but ecpg returned error on the last
> line.

Sure. First of all there must not be an equal sign. And second ECPG does not
accept a variable here. 

> Can a variable name be used in the offset option to pass
> the value ?

Right now: No. But surely this should be done.

Michael
-- 
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael@Fam-Meskes.De           | Use PostgreSQL!

Re: How do I select nth row from a table

From
Michael Meskes
Date:
On Wed, Apr 19, 2000 at 11:14:13PM -0400, Tom Lane wrote:
> It looks like not, at the moment.  I think the grammar for ecpg

Right.

> needs to accept a "cvariable" in places where the underlying
> backend grammar only accepts an ICONST.  It's probably too late
> to fix this for 7.0, but it should be a todo item for 7.1...

I agree.

Michael
-- 
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael@Fam-Meskes.De           | Use PostgreSQL!