Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Questions getting random records

2 posts in Windows NT Last posting was on 2001-04-29 16:21:43.0Z
Kevin Posted on 2001-04-27 21:22:42.0Z
From: Kevin
Date: Fri, 27 Apr 2001 17:22:42 -0400
Newsgroups: sybase.public.sqlserver.nt
Subject: Questions getting random records
Message-ID: <6C39CC82BBDB5ED500756F6C85256A3B.00756F7B85256A3B@webforums>
Lines: 7
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.sqlserver.nt:873
Article PK: 1087480

Hi All,
I have a table(T1) which contains 54878 records and I need to get 4000
random records from T1 and insert into another table (T2). Could anyone
tell me what's the best way of doing it?


Kevin


Jim Egan Posted on 2001-04-29 16:21:43.0Z
From: Jim Egan <dbaguru@eganomics.com>
Subject: Re: Questions getting random records
Date: Sun, 29 Apr 2001 10:21:43 -0600
Message-ID: <MPG.1555ed8663d889f398b2fe@forums.sybase.com>
References: <6C39CC82BBDB5ED500756F6C85256A3B.00756F7B85256A3B@webforums>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.50
Newsgroups: sybase.public.sqlserver.nt
Lines: 24
NNTP-Posting-Host: c1420400-b.hiland1.co.home.com 65.7.153.228
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:869
Article PK: 1087478


Kevin wrote...
> Hi All,
> I have a table(T1) which contains 54878 records and I need to get 4000
> random records from T1 and insert into another table (T2). Could anyone
> tell me what's the best way of doing it?
>
>
> Kevin
>

1. Copy the keys from T1 into a temp table that has an identity column.
2. Within a loop get a random value that ranges from 1 to (number of rows in temp table).
3. Match the random value to the identity column in the temp table to get the key value
from table T1.
4. Delete the row in the temp table after it is used and decrement the number of rows in
the temp table.
4. Loop to step 2 until 4000 rows have been removed from the temp table.

This may not be the "best" method but it's easy and doesn't require a cursor.
--
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services

Get your free subscription to PowerTimes at http://www.powertimes.com