Thread: Row Count
How can I get selected row count in one statement?
For example, I can do that by two select statements
select id, name, addrress, (select count(*) from(select id
from users
where name<>'examplename')
from users
where name<>'examplename'
or
declare rowcount integer
...............
rowcount:=select count(*) from(select id
from users
where name<>'examplename') as rst
select id, name, address, rowcount as rowcount
from users
where name<>'examplename'
For example, I can do that by two select statements
select id, name, addrress, (select count(*) from(select id
from users
where name<>'examplename')
from users
where name<>'examplename'
or
declare rowcount integer
...............
rowcount:=select count(*) from(select id
from users
where name<>'examplename') as rst
select id, name, address, rowcount as rowcount
from users
where name<>'examplename'
Sergey Gigoyan, 12.02.2013 08:05: > How can I get selected row count in one statement? > For example, I can do that by two select statements > > select id, name, addrress, (select count(*) from(select id > from users > where name<>'examplename') > from users > where name<>'examplename' > select id, name, addrress, count(*) over () as row_count from users where name <> 'examplename';
Sergey Gigoyan <sergey.gigoyan@gmail.com> hat am 12. Februar 2013 um 08:05 geschrieben: > How can I get selected row count in one statement? > For example, I can do that by two select statements > > select id, name, addrress, (select count(*) from(select id select id, name, ..., row_number() over () from ... works since 8.4. Andreas
On Mon, Feb 11, 2013 at 11:05 PM, Sergey Gigoyan <sergey.gigoyan@gmail.com> wrote: > How can I get selected row count in one statement? Usually it is immediately available after statement execution without additional query. For example in PL/PgSQL you can use GET DIAGNOSTICS straight after your SELECT: SELECT INTO _id, _name, _address id, name, address FROM users WHERE name <> 'examplename'; GET DIAGNOSTICS _row_count = ROW_COUNT; In external languages like Perl: $sth = $dbh->prepare( "select id, name, address, rowcount as rowcount". "from users where name<>'examplename'"); $row_count = $sth->execute(...); > For example, I can do that by two select statements > > select id, name, addrress, (select count(*) from(select id > from > users > where > name<>'examplename') > from users > where name<>'examplename' > > or > > declare rowcount integer > ............... > rowcount:=select count(*) from(select id > from users > where name<>'examplename') > as rst > > select id, name, address, rowcount as rowcount > from users > where name<>'examplename' > -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray.ru@gmail.com
Andreas Kretschmer, 12.02.2013 09:03: > select id, name, ..., row_number() over () from ... > > works since 8.4. > That will only return the row count in the *last* row, not in all of them.