rowcount

mysql: rownum functionality

Thursday, January 31st, 2008

Select, update etc…

UPDATE mytable
SET col1 = ’somevalue’
ORDER BY col2
LIMIT 300

rownum analog:

SELECT @rownum:=@rownum+1 rownum, mytable.*
FROM (SELECT @rownum:=0) r, mytable;

Quick calculation of the row count

Thursday, March 30th, 2006

Warning! It depends on the statistics and could be not accurate!
Sybase
select user_name( obj.uid), obj.name , rowcnt(doampg)
from sysindexes idx, sysobjects obj
where idx.indid < 2 and idx.id = obj.id and obj.type = ‘U’
order by 1,2
go
select object_name( idx.id ), rowcnt(doampg)
from sysindexes idx
where idx.indid < 2
and object_name( idx.id ) in (’object1′,’object2′)
go
Oracle
select OWNER, TABLE_NAME, NUM_ROWS
from dba_tables
where TABLE_NAME = upper( ‘&what_tbl.’ [...]

Keep on coding