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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Oliveiros Cristina | 2008-10-09 16:49:56 | Re: sequence number in a result |
Previous Message | Campbell, Lance | 2008-10-09 16:31:05 | sequence number in a result |