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
