| getting rowcount as part of the select field [message #23347] |
Tue, 03 December 2002 18:55  |
Michael
Messages: 61
Registered: October 1999
|
Member |
|
|
Is there a way I can get the row count as one of the column in the result set of a select statement in Oracle:
ie.
select count##, name, description, id
from registered_user
where name like 'Joe%'
order by id
The result would be, assuming count## is the method to get the # of actual result of the SQL statement:
3, Joey Ve, selfRegistered, 12345
3, Joe Holland, selfRegistered, 12346
3, Joe Durant, administrator, 44566
|
|
|
|
| Re: getting rowcount as part of the select field [message #23348 is a reply to message #23347] |
Tue, 03 December 2002 20:34  |
 |
Barbara Boehmer
Messages: 9094
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SQL> -- test data:
SQL> SELECT name, description, id
2 FROM registered_user
3 /
NAME DESCRIPTION ID
-------------------- -------------------- ----------
Joey Ve selfRegistered 12345
Joe Holland selfRegistered 12346
Joe Durant administrator 44566
John Doe nobody 99999
SQL>
SQL>
SQL> -- query WITHOUT count:
SQL> SELECT name, description, id
2 FROM registered_user
3 WHERE name LIKE 'Joe%'
4 ORDER BY id
5 /
NAME DESCRIPTION ID
-------------------- -------------------- ----------
Joey Ve selfRegistered 12345
Joe Holland selfRegistered 12346
Joe Durant administrator 44566
SQL>
SQL>
SQL> -- count of above records:
SQL> SELECT COUNT (*) AS row_count
2 FROM registered_user
3 WHERE name LIKE 'Joe%'
4 /
ROW_COUNT
----------
3
SQL>
SQL>
SQL> -- query WITH count:
SQL> SELECT row_count, name, description, id
2 FROM registered_user,
3 (SELECT COUNT (*) AS row_count
4 FROM registered_user
5 WHERE name LIKE 'Joe%')
6 WHERE name LIKE 'Joe%'
7 ORDER BY id
8 /
ROW_COUNT NAME DESCRIPTION ID
---------- -------------------- -------------------- ----------
3 Joey Ve selfRegistered 12345
3 Joe Holland selfRegistered 12346
3 Joe Durant administrator 44566
SQL>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
