Re: SQL syntax rowcount value as an extra column in the result set - Mailing list pgsql-sql

<font face="sans-serif" size="2">Hi,</font><br /><font face="sans-serif" size="2">Is this what you are trying to
do?</font><br/><font face="sans-serif" size="2">postgres=# select * from (select count(*) from people ) p, (select
firstnamefrom people)p2;</font><br /><font face="sans-serif" size="2"> count | firstname</font><br /><font
face="sans-serif"size="2">-------+-----------</font><br /><font face="sans-serif" size="2">     5 | Mary</font><br
/><fontface="sans-serif" size="2">     5 | Mary</font><br /><font face="sans-serif" size="2">     5 | John</font><br
/><fontface="sans-serif" size="2">     5 | John</font><br /><font face="sans-serif" size="2">     5 | Jacob</font><br
/><fontface="sans-serif" size="2">(5 rows)</font><br /><font face="sans-serif" size="2">I do not know about the
performanceimpact of such a query (cartesian join)</font><br /><font face="sans-serif" size="2">Regards,</font><br
/><fontface="Trebuchet MS" size="2">Jayadevan</font><br /><br /><br /><br /><br /><font color="#5f5f5f"
face="sans-serif"size="1">From:        </font><font face="sans-serif" size="1">"Snyder, James"
<jsnyde07@harris.com></font><br/><font color="#5f5f5f" face="sans-serif" size="1">To:        </font><font
face="sans-serif"size="1"><pgsql-sql@postgresql.org></font><br /><font color="#5f5f5f" face="sans-serif"
size="1">Date:       </font><font face="sans-serif" size="1">26/03/2010 03:21</font><br /><font color="#5f5f5f"
face="sans-serif"size="1">Subject:        </font><font face="sans-serif" size="1">[SQL] SQL syntax rowcount value as an
extracolumn in the result set</font><br /><font color="#5f5f5f" face="sans-serif" size="1">Sent by:        </font><font
face="sans-serif"size="1">pgsql-sql-owner@postgresql.org</font><br /><hr noshade /><br /><br /><br /><font
face="Calibri"size="3">Hello</font><p><font face="Calibri" size="3">I’m using PostgreSQL (8.4.701) and Java
(jdbc,</font><fontsize="3"> </font><font face="Calibri" size="3">postgresql-8.4-701.jdbc4.jar) to connect to the
database.</font><p><fontface="Calibri" size="3">My question is: what is the SQL syntax for PostgreSQL to achieve the
following:</font><p><fontface="Calibri" size="3">I want to receive the rowcount along with the rest of a result set.
Forexample, let’s say the following query returns</font><p><font face="Calibri" size="3">select first_name from
people;</font><p><fontface="Calibri" size="3">first_name</font><p><font face="Calibri"
size="3">=========</font><p><fontface="Calibri" size="3">Mary</font><p><font face="Calibri" size="3">Sue</font><p><font
face="Calibri"size="3">Joe</font><p><p><font face="Calibri" size="3">and the following query returns the
value</font><fontsize="3"></font><p><font face="Calibri" size="3">select count(*)as ROWCOUNT</font><font
size="3"></font><fontface="Calibri" size="3">from people;</font><p><font face="Calibri"
size="3">ROWCOUNT</font><p><fontface="Calibri" size="3">==========</font><p><font face="Calibri"
size="3">3</font><p><fontface="Calibri" size="3">3</font><p><font face="Calibri" size="3">What I’m looking for is the
outputas</font><p><font face="Calibri" size="3">ROWCOUNT ,  first_name</font><p><font face="Calibri"
size="3">=====================</font><p><fontface="Calibri" size="3">3 , Mary</font><p><font face="Calibri" size="3">3
,Sue</font><p><font face="Calibri" size="3">3 , Joe</font><p><font face="Calibri" size="3">so I can use JDBC (snip-it)
asfollows:</font><p><font face="Calibri" size="3">resultSet.getInt(“ROWCOUNT”)</font><p><font face="Calibri"
size="3">resultSet.getString(“first_name”)</font><p><fontface="Calibri" size="3">On a side note,</font><font size="3">
</font><fontface="Calibri" size="3">Oracle allows the following</font><font size="3"> </font><font face="Calibri"
size="3">syntaxto achieve the above:</font><p><font face="Calibri" size="3">select count(*) over ()</font><font
size="3"></font><fontface="Calibri" size="3">as ROWCOUNT</font><font size="3"> </font><font face="Calibri" size="3">,
first_name</font><fontsize="3"> </font><font face="Calibri" size="3">from people</font><p><font face="Calibri"
size="3">Thanks,Jim</font><p><fontface="sans-serif"></font><img
src="http://www.ibsplc.com/images/email_footer_final.gif"/> <font color="#999999" face="Tahoma" size="1"><u>
DISCLAIMER:</u></font> <font color="#999999" face="Tahoma" size="1"> "The information in this e-mail and any attachment
isintended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you
havereceived this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS
makesno warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information
containedin this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant
lossor damage, if any, direct or indirect."</font><br /><font size="1"> </font>