Thread: returned row number

returned row number

From
"Keith Worthington"
Date:
Hi All,

I have a quick question.  Is there a simple way to access the returned record
number?

I want to be able to do something like

SELECT record AS record_no,
       column1,
       column2
  FROM table1
 WHERE column2 < 100
 ORDER BY column1;

record_no | column1 | column2
----------+---------+---------
1         | A       | 1.75
2         | F       | 93
3         | M       | 12.5
4         | N       | 0
5         | Q       | 57.6

Kind Regards,
Keith

Re: returned row number

From
Bruno Wolff III
Date:
On Fri, May 06, 2005 at 15:45:02 -0400,
  Keith Worthington <keithw@narrowpathinc.com> wrote:
> Hi All,
>
> I have a quick question.  Is there a simple way to access the returned record
> number?
>
> I want to be able to do something like
>
> SELECT record AS record_no,
>        column1,
>        column2
>   FROM table1
>  WHERE column2 < 100
>  ORDER BY column1;
>
> record_no | column1 | column2
> ----------+---------+---------
> 1         | A       | 1.75
> 2         | F       | 93
> 3         | M       | 12.5
> 4         | N       | 0
> 5         | Q       | 57.6

Normally the best thing to do is have the application count.
You can use a subquery to get the counts, but it will be a
lot slower. You could also write a set returning function
to do this.

Re: returned row number

From
"Keith Worthington"
Date:
On Fri, 6 May 2005 16:00:16 -0500, Bruno Wolff III wrote
> On Fri, May 06, 2005 at 15:45:02 -0400,
>   Keith Worthington <keithw@narrowpathinc.com> wrote:
> > Hi All,
> >
> > I have a quick question.  Is there a simple way to access
> > the returned record number?
> >
> > I want to be able to do something like
> >
> > SELECT record AS record_no,
> >        column1,
> >        column2
> >   FROM table1
> >  WHERE column2 < 100
> >  ORDER BY column1;
> >
> > record_no | column1 | column2
> > ----------+---------+---------
> > 1         | A       | 1.75
> > 2         | F       | 93
> > 3         | M       | 12.5
> > 4         | N       | 0
> > 5         | Q       | 57.6
>
> Normally the best thing to do is have the application count.
> You can use a subquery to get the counts, but it will be a
> lot slower. You could also write a set returning function
> to do this.
>

This query is actually in a SRF.

Can you suggest how to proceed?

Kind Regards,
Keith

Re: returned row number

From
"Celia McInnis"
Date:
How to I convert from EPOCH time to a "TIMESTAMP WTHOUT TIME ZONE" type?


convert from epoch to timestamp

From
"Celia McInnis"
Date:
This time I'll give an appropriate subject...

How to I convert from EPOCH time to a "TIMESTAMP WTHOUT TIME ZONE" type?

Celia McInnis
(who's tired and wants to go home...)

Re: convert from epoch to timestamp

From
Bruno Wolff III
Date:
On Fri, May 06, 2005 at 19:39:26 -0500,
  Celia McInnis <celia@drmath.ca> wrote:
> This time I'll give an appropriate subject...

Please don't reply to existing threads to start a new one, even if you change
the subject.

> How to I convert from EPOCH time to a "TIMESTAMP WTHOUT TIME ZONE" type?

The simplest is probably:
select 1::abstime at time zone 'UTC';
Where you replace '1' by whatever number has the integer number of seconds
since the epoch. If you need fractional seconds then you need to multiply
the number seonds times and interval of 1 second and add it to the
the start of the epoch.

Re: returned row number

From
Bruno Wolff III
Date:
On Fri, May 06, 2005 at 17:00:21 -0400,
  Keith Worthington <keithw@narrowpathinc.com> wrote:
> On Fri, 6 May 2005 16:00:16 -0500, Bruno Wolff III wrote
> > On Fri, May 06, 2005 at 15:45:02 -0400,
> >   Keith Worthington <keithw@narrowpathinc.com> wrote:
> > > Hi All,
> > >
> > > I have a quick question.  Is there a simple way to access
> > > the returned record number?
> > >
> > > I want to be able to do something like
> > >
> > > SELECT record AS record_no,
> > >        column1,
> > >        column2
> > >   FROM table1
> > >  WHERE column2 < 100
> > >  ORDER BY column1;
> > >
> > > record_no | column1 | column2
> > > ----------+---------+---------
> > > 1         | A       | 1.75
> > > 2         | F       | 93
> > > 3         | M       | 12.5
> > > 4         | N       | 0
> > > 5         | Q       | 57.6
> >
> > Normally the best thing to do is have the application count.
> > You can use a subquery to get the counts, but it will be a
> > lot slower. You could also write a set returning function
> > to do this.
> >
>
> This query is actually in a SRF.
>
> Can you suggest how to proceed?

If you are looping through the results in the SRF, you should be able to
increment a counter as you are doing that (unless the language is SQL).
I don't do enough of that to be able to write an example off the top
of my head, but it shouldn't be hard to figure out.