Query Recordcount 1

[matrixindicator]

Hey,

I have a query of about 1.580.000 records.
I need to get tree parts in the underlying table.
1. 500.000
2. 500.000
3. 580.000

The best way (fast) is to do this with a few delete queries.
For the first part I need to say delete everyting that is > 500.000. Can I use something like recordcount and how do I write this in the where condition

 

[jrbarnett]

To get the first 500,000

SELECT TOP 500000 field1, field2, field3
FROM yourtable
ORDER BY field1

Note that the order by is important as otherwise it will just use the first 500000 records that get retrieved.

You can then use that as a subquery for deleting from the main table. Repeat the same query for the second 500,000 and what is left is the 580,000 records.

John

 

[randy700]

If I understand your requirement, you need to break the query down to 3 separate queries?

How about...

Q1:
SELECT TOP 500000 *
FROM tblNameOfTable

Q2:
SELECT TOP 500000 *
FROM tblNameOfTable
WHERE PrimaryKey Not IN(Q1)

Q2:
SELECT *
FROM tblNameOfTable
WHERE PrimaryKey Not IN(Q1)
AND PrimaryKey Not IN(Q2)


Randy

 

[matrixindicator]

thanks Randy, I think its a good option to do this your way.
Easy and a correct result.