Simple query

N

Noozer

What SQL would I use to delete the oldest record, if I had more than 5,000
records? Better yet, if there are more than 5,000 records, delete all the
oldest until there are less than 5,000.

My table includes a column named "Added" which contains the date that the
record was added.

Thx!
 
P

peregenem

Noozer said:
What SQL would I use to delete the oldest record, if I had more than 5,000
records? Better yet, if there are more than 5,000 records, delete all the
oldest until there are less than 5,000.
Click to expand...

DELETE FROM MyTable
WHERE date_added = (
SELECT MIN(T1.date_added)
FROM MyTable AS T1)
AND 5000 < (SELECT COUNT(*) FROM MyTable AS T2)

You could keep calling this until rows affected equal zero.
 
P

peregenem

A better solution, to be called just once

DELETE FROM MyTable
WHERE date_added <= (
SELECT MIN(DT1.date_added)
FROM (
SELECT T1.date_added, (
SELECT COUNT(*)
FROM MyTable AS T2
WHERE T2.date_added <= T1.date_added
) AS rollup
FROM MyTable AS T1
) AS DT1
WHERE DT1.rollup > 5000);
 
P

peregenem

A better solution ...
Click to expand...

Oops! Signs round the wrong way. Correction in full

DELETE FROM MyTable
WHERE date_added >= (
SELECT MIN(DT1.date_added)
FROM (
SELECT T1.date_added, (
SELECT COUNT(*)
FROM MyTable AS T2
WHERE T2.date_added <= T1.date_added
) AS rollup
FROM MyTable AS T1
) AS DT1
WHERE DT1.rollup > 5000);
 
P

peregenem

Correction ...
Click to expand...

....and then I actually tested it. How to make a simple query confusing,
eh? :)

DELETE FROM MyTable
WHERE date_added <= (
SELECT MAX(DT1.date_added)
FROM (
SELECT T1.date_added, (
SELECT COUNT(*)
FROM MyTable AS T2
WHERE T2.date_added >= T1.date_added
) AS rollup
FROM MyTable AS T1
) AS DT1
WHERE DT1.rollup > 5000);
 
A

Anders

Hi,

I have made a parameter quarry that asks what a firm is called [What is the
firm name?] And that works just perfect as long as i spell the firm
correctly. What I was wondering is how can I make the parameter querries so
that if the firm name is Microsoft and i just type Micro I get up both
Microsoft and for example Micro office another company?

Thank you so much

Kind Regards
 
P

peregenem

Anders said:
how can I make the parameter querries so
that if the firm name is Microsoft and i just type Micro I get up both
Microsoft and for example Micro office another company?
Click to expand...

CurrentProject.Connection.Execute _
" CREATE PROCEDURE GetCompanies (" & _
":company_name VARCHAR(255) = NULL" & _
") AS" & _
" SELECT * FROM Companies" & _
" WHERE company_name LIKE :company_name & '%';"
 
J

John Spencer (MVP)

Change the criteria to

Like [What is the firm name?] & "*"

Like uses wildcards and the * means zero to many characters. So this is
basically looking for a match that begins with whatever you respond. If you
respond by leaving the prompt blank, you will get all records that have a value
in the field, because nothing followed by anything is going to be a match. It
will not pick up fields which are null (usually appear as blank).
 
Top