How to delet a single record based on an integer and a date?

Databases
1

Hello all,

I have a table that among other things, holds an integer value and a SQL DateTime value. There may be multiples of the integer, but the DateTime will vary. I would like to delete a single record of the ground of the same integer value, but select the oldest DateTime to delete. Can anyone suggest the SQL code or process to do this?

What I was planning on, was to retrieve all of the records for the integer value, but not sure how to ask SQL for the lowest (or highest) DateTime value. However, once this was had, I could delete the record based on its rowid.

Thoughts anyone?
Thank you,
Tim

2 
delete from mytable a
where integer_field=123
  and date_field=(select max(date_field) from mytable b where a.integer_field=b.integer_field)
3

Thanks Dave,
But what do you do, if you do not know the DateTime? Just want either the earliest or latest to be deleted?
Tim

4

You use the code I provided… no where does it ask for a specific date/time… if you want the latest use MAX (as in the code) for the earliest change that to MIN.

123 is the integer value you are seeking…

5

Sorry Dave, I misread your code! Will give that a try now! Thanks for pointing that out to me!
TIm

6

Dave,

I get a SQL Syntax error near ‘a’ when executing this code:

db.SQLexecute("delete FROM onsite a WHERE access_code = "+ sPIN + " AND sql_date_time = (SELECT max(sql_date_time) FROM onsite b WHERE a.access_code = b.access_code)" )

However when executing this code, it does not give an error:

RsOnSite= db.SQLSelect("SELECT * FROM onsite a WHERE access_code = "+ sPIN + " AND sql_date_time = (SELECT min(sql_date_time) FROM onsite b WHERE a.access_code = b.access_code)" )

The second one is a SELECT, works perfectly. The first, the DELETE Command creates a recordset with the correct data, but does not delete anything.

Can you see where my mistake is?
Thank you!
Tim

7

You mean the 2nd one creates a record set… as SQLExecute does not create anything…
Does the SELECT return the record that you have expected to be deleted?

8

Hi Dave, thank you again for your reply!
Yes, the SELECT does return the correct record. And if I do, as a separate operation a delete on that record ID, it works. But your original code idea, to get and delete the the record in one shot, is far better!

Tim