Skip site navigation (1) Skip section navigation (2)

Re: sequence number in a result

From: "Oliveiros Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
To: "Campbell, Lance" <lance(at)illinois(dot)edu>,<pgsql-sql(at)postgresql(dot)org>
Subject: Re: sequence number in a result
Date: 2008-10-09 16:48:00
Message-ID: 06a401c92a2e$ca6d4230$ec5a3d0a@marktestcr.marktest.pt (view raw or flat)
Thread:
Lists: pgsql-sql
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(at)postgresql(dot)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

   

In response to

pgsql-sql by date

Next:From: Oliveiros CristinaDate: 2008-10-09 16:49:56
Subject: Re: sequence number in a result
Previous:From: Campbell, LanceDate: 2008-10-09 16:31:05
Subject: sequence number in a result

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group