Thread: returned row number
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
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.
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
How to I convert from EPOCH time to a "TIMESTAMP WTHOUT TIME ZONE" type?
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...)
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.
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.