SQL syntax rowcount value as an extra column in the result set
| От | Snyder, James |
|---|---|
| Тема | SQL syntax rowcount value as an extra column in the result set |
| Дата | |
| Msg-id | 594D625E4784AD4EA9D423E292BE6725489FD1@mlbe2k10.cs.myharris.net обсуждение исходный текст |
| Ответы |
Re: SQL syntax rowcount value as an extra column in the result set
(Thomas Kellerer <spam_eater@gmx.net>)
Re: SQL syntax rowcount value as an extra column in the result set ("Jayadevan M" <Jayadevan.Maymala@ibsplc.com>) |
| Список | pgsql-sql |
<p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><font face="Calibri">Hello</font></span><pdir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><pdir="LTR"><span lang="en-us"><font face="Calibri">I</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">’</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">m using PostgreSQL (8.4.701) and Java (jdbc</font></span><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"><font face="Calibri">,</font></span><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"> <font face="Calibri">postgresql-8.4-701.jdbc4.jar</font></span><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"><fontface="Calibri">) to connect to the database.</font></span><p dir="LTR"><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"><font face="Calibri">Myquestion is:</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><font face="Calibri">what is the SQL syntax for PostgreSQL to achieve the following:</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"><font face="Calibri">I want to receive the rowcountalong with the rest of a result set. For example, let</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">’</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">s say the following query returns</font></span><p dir="LTR"><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">select first_name from people</font></span><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"><font face="Calibri">;</font></span><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p dir="LTR"><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">first_name</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"><font face="Calibri">=========</font></span><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p dir="LTR"><spanlang="en-us"><font face="Calibri">Mary</font></span><p dir="LTR"><span lang="en-us"><font face="Calibri">Sue</font></span><pdir="LTR"><span lang="en-us"><font face="Calibri">Joe</font></span><br /><p dir="LTR"><spanlang="en-us"><font face="Calibri">and the following query returns the value</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"> </span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><fontface="Calibri">select count(*)</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><fontface="Calibri">as ROWCOUNT</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><fontface="Calibri"></font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"> <fontface="Calibri">from people</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><fontface="Calibri">;</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><pdir="LTR"><span lang="en-us"><font face="Calibri">ROWCOUNT</font></span><p dir="LTR"><span lang="en-us"><fontface="Calibri">==========</font></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">3</font></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">3</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><pdir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p dir="LTR"><spanlang="en-us"><font face="Calibri">What I</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">’</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">m looking for is the output as</font></span><p dir="LTR"><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">ROWCOUNT , first_name</font></span><pdir="LTR"><span lang="en-us"><font face="Calibri">=====================</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"><font face="Calibri">3, Mary</font></span><p dir="LTR"><span lang="en-us"><font face="Calibri">3 , Sue</font></span><p dir="LTR"><spanlang="en-us"><font face="Calibri">3 , Joe</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><pdir="LTR"><span lang="en-us"><font face="Calibri">so I can use JDBC (snip-it) as follows:</font></span><pdir="LTR"><span lang="en-us"><font face="Calibri">resultSet.getInt(</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">“</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">ROWCOUNT</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">”</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">)</font></span><p dir="LTR"><span lang="en-us"><fontface="Calibri">resultSet.getString(</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">“</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">first_name</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">”</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">)</font></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><fontface="Calibri">On a side note,</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><font face="Calibri">Oracle allows the following</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"> <font face="Calibri">syntax</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri"> to achieve the above</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">:</font></span><p dir="LTR"><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">select count(*)</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri"> over ()</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"> <font face="Calibri">as ROWCOUNT</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri"></font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"> <font face="Calibri">, first_name</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"> <font face="Calibri">from people</font></span><p dir="LTR"><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"><fontface="Calibri">Thank</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><fontface="Calibri">s,</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><fontface="Calibri">Jim</font></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"></span>