Thread: sequence number in a result

<div class="Section1"><p class="MsoNormal">Say I have the following SQL statement:<p class="MsoNormal"> <p
class="MsoNormal">SELECTa, b, c FROM t1 ORDER BY a;<p class="MsoNormal"> <p class="MsoNormal">Is there a function or
specialsystem label I can use that would generate a sequence number in the returning result set?<p
class="MsoNormal"> <pclass="MsoNormal">Example:<p class="MsoNormal">SELECT a, b, c, SOMELABEL as order FROM t1 ORDER BY
a;<pclass="MsoNormal"> <p class="MsoNormal">Result:<p class="MsoNormal">a    b     c   order<p
class="MsoNormal">---------------------<pclass="MsoNormal">Aa  bb  cc  1<p class="MsoNormal">A1  bb  cc  2<p
class="MsoNormal">A2 bb  cc  3<p class="MsoNormal"> <p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Thanks,</span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""> </span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">LanceCampbell</span><span
style="font-size:12.0pt;font-family:"TimesNew Roman","serif""></span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">ProjectManager/Software Architect</span><p
class="MsoNormal"><spanstyle="font-size:10.0pt;font-family:"Arial","sans-serif"">Web Services at Public
Affairs</span><pclass="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">University of
Illinois</span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">217.333.0382</span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""><ahref="http://webservices.uiuc.edu/"><span
style="color:blue">http://webservices.uiuc.edu</span></a></span><pclass="MsoNormal"> </div> 

----- Original Message -----

From: Campbell, Lance

To: pgsql-sql@postgresql.org

Sent: Thursday, October 09, 2008 5:31 PM

Subject: [SQL] sequence number in a result

Say I have the following SQL statement:

 

SELECT a, b, c FROM t1 ORDER BY a;

 

Is there a function or special system label I can use that would generate a sequence number in the returning result set?

 

Example:

SELECT a, b, c, SOMELABEL as order FROM t1 ORDER BY a;

 

Result:

a    b     c   order

---------------------

Aa  bb  cc  1

A1  bb  cc  2

A2  bb  cc  3

 

Thanks,

 

Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu

 

----- Original Message -----

From: Oliveiros Cristina

To: Campbell, Lance ; pgsql-sql@postgresql.org

Sent: Thursday, October 09, 2008 5:48 PM

Subject: Re: [SQL] sequence number in a result

Howdy, Lance.

 

I had that problem about a year ago, and AFAIK there is no solution, at least not in SQL Standard.

 

What I did was something like

 

SELECT a,b,c,count(y.a) as order

FROM t1 x , t1 y

WHERE  ((x.a >  y.a)

OR (x.a = y.a

AND x.ID <= y.ID))   -- Use here whatever you have as primary key on your table...

GROUP BY x.a,x.b,x.c ;

ORDER BY a ;

 

But this trick is just for relatively small tables.

When I needed something for bigger tables, I did it programmatically

 

But, maybe PostGreSQL has some proprietary function which I dunno that can do precisely this...

 

HTH a little...

 

Best,

Oliveiros 

"(SELECT resumo1.\"iPages\",resumo1.\"iUniqueVisitors\",resumo1.\"IDSite\",resumo1.\"IDWeboramaAccount\",resumo1.\"Visits\", " +

"COUNT(resumo2.\"iPages\") as rank " +

"FROM " + m_strSUBQUERY_INTERFACE + " resumo1," +

" " + m_strSUBQUERY_INTERFACE + " resumo2 " +

"WHERE ((resumo1.\"dtDate\" = @diadehoje) " +

"AND (resumo2.\"dtDate\" = @diadehoje)) " +

"AND ((resumo1.\"iPages\" < resumo2.\"iPages\" ) " +

"OR (resumo1.\"iPages\" = resumo2.\"iPages\" " +

"AND resumo1.\"IDSiteResume\" <= resumo2.\"IDSiteResume\")) " + // Tinha e tem IDSiteResume

"GROUP BY resumo1.\"iPages\",resumo1.\"iUniqueVisitors\"," +

"resumo1.\"IDSite\",resumo1.\"dtDate\",resumo1.\"IDSiteResume\",resumo1.\"IDWeboramaAccount\",resumo1.\"Visits\"

 

----- Original Message -----

From: Campbell, Lance

To: pgsql-sql@postgresql.org

Sent: Thursday, October 09, 2008 5:31 PM

Subject: [SQL] sequence number in a result

Say I have the following SQL statement:

 

SELECT a, b, c FROM t1 ORDER BY a;

 

Is there a function or special system label I can use that would generate a sequence number in the returning result set?

 

Example:

SELECT a, b, c, SOMELABEL as order FROM t1 ORDER BY a;

 

Result:

a    b     c   order

---------------------

Aa  bb  cc  1

A1  bb  cc  2

A2  bb  cc  3

 

Thanks,

 

Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu

 

"Campbell, Lance" <lance@illinois.edu> writes:
> Is there a function or special system label I can use that would
> generate a sequence number in the returning result set?

The usual hack is a temporary sequence:

regression=# create temp sequence s1;
CREATE SEQUENCE
regression=# select nextval('s1'), * from (select * from int8_tbl order by q1) ss;nextval |        q1        |
q2        
 
---------+------------------+-------------------      1 |              123 |               456      2 |
123|  4567890123456789      3 | 4567890123456789 |               123      4 | 4567890123456789 |  4567890123456789
5| 4567890123456789 | -4567890123456789
 
(5 rows)

Note that you must use a subselect to ensure that the sequence number
gets stuck on *after* the ORDER BY happens, else what you'll probably
get is numbering corresponding to the unsorted row order.

It would be possible to write a C function to do this with a lot less
overhead than a sequence entails, but no one's got round to it AFAIK.
        regards, tom lane

>  Is there a function or special system label I can use that would
generate a sequence number in the returning result set?


Would something like this work for you?

CREATE TEMP SEQUENCE foo;
SELECT a, b, c, nextval('foo') AS order FROM t1 ORDER BY a;

Mike Relyea
Product Development Engineer
Xerox Corporation
Building 218 Room 107
800 Phillips Rd
Webster, NY 14580

p 585.265.7321