Re: who is the 500000th user

From: Michael Sun <mikeny31_at_speakeasy.org>
Date: Sun, 30 Apr 2000 00:21:23 -0400
Message-Id: <10482.104512@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_0220_01BFB23A.04509360 Content-Type: text/plain;

charset="gb2312"
Content-Transfer-Encoding: quoted-printable

ROWNUM is a function applied only to the recordset (query results), not = to the=20
table itself.

So the following query will get the max(user_id) on the table, select = all records from the table,
then limit the output to the first 500000 rows, and give you a count.

> SQL> select count(*) , max(user_id) from userbasicinfo where = rownum<500001;
>=20

While the next query will count all users in this table (because user_id = clause uses the=20
maximum value).
>=20
> SQL> select count(*) from userbasicinfo where user_id<=3D524351;

------=_NextPart_000_0220_01BFB23A.04509360 Content-Type: text/html;

charset="gb2312"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Dgb2312" http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2919.6307" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><STRONG><FONT face=3DArial size=3D2>ROWNUM is a function applied =
only to the=20
recordset (query results), not to the </FONT></STRONG></DIV>
<DIV><FONT face=3DArial size=3D2><STRONG>table =
itself.</STRONG></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><BR><STRONG>So the following query will =
get the=20
max(user_id) on the table, select all records from the=20 table,</STRONG></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><STRONG>then limit the output to the =
first 500000=20
rows, and give you a count.</STRONG></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>> SQL> select count(*) , =
max(user_id) from=20
userbasicinfo where rownum<500001;<BR>> <BR></FONT></DIV>
<DIV><STRONG><FONT face=3DArial size=3D2>While the next query will count =
all users=20
in this table (because user_id clause uses the </FONT></STRONG></DIV>
<DIV><FONT face=3DArial size=3D2><STRONG>maximum =
value).</STRONG></DIV></FONT>
<DIV><FONT face=3DArial size=3D2>> <BR>> SQL> select count(*) =
from=20
userbasicinfo where =
user_id<=3D524351;<BR><BR></FONT></DIV></BODY></HTML> Received on Sat Apr 29 2000 - 23:21:23 CDT