Selecting ten random records from 1000

[rilkyn]

I have a table with the following three columns:

SITE_COD
BUS_CAT_COD
BUS_TYP_COD

SITE_COD and BUS_CAT_COD are used as an index to provide a unique list of category codes per site code. Each BUS_CAT_COD then has a number of BUS_TYP_COD records related.

So for example


SITE_COD BUS_CAT_COD Number of BUS_TYP_COD records

0 APPL 1
0 CNTRYCOD 100
1 APPL 98
2 CNTRYCOD 12341


What I need to do is delete all records bar ten per per SITE_COD/BUS_CAT_COD (where there are more than 10 records). If possible I would like to get a random selection of records but I don't think this is possible.

The reason for doing this is the above live table(which has a larger number of columns than in the example) currently has more than 1,000,000 records and we need to run a baseline test with some jobs that could potentially produce in excess of 600,000,000 tests. If we reduce the table size then the number of tests could be reduced to something like 1,000,000. Still a huge number but with automation, possible.

I have managed to produce a record count based on SITE_COD and BUS_CAT_COD but am not sure how to go about deleting records after the tenth record for any combination containing more thant 10 records.

Any help would be greatfully appreciated.

 

[PHV]

Instead of deleting records you may consider a maketable query:
SELECT A.* INTO NewTable
FROM yourTable AS A
WHERE A.BUS_TYP_COD In (SELECT TOP 10 B.BUS_TYP_COD
FROM yourTable AS B WHERE B.SITE_COD=A.SITE_COD AND B.BUS_CAT_COD=A.BUS_CAT_COD ORDER BY Rnd(B.SITE_COD));

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244