save top N number of records in table and delete rest of records

[kskinne]

I need a query that will determine the top 4 records in my table that meet some criteria in a specific WHERE clause, then the rest of the records that meet the WHERE criteria but are NOT among the top 4 records based on my ORDER BY, would be deleted. Is this possible?

Thank you,
Kevin

 

[SQLSister]

yes of course it is possible but it is certainly an unual request.

first selct the four records you want to keep into a temp table or table variable.
Then write your delete left joining to the temp table and making sure to add the where clause.
(untested code follows)

select top 4 id into #temp from Table1
where field1 = 'test'
order by field2 

delete t1 
from table1 t1
left join #temp t2 on t1.id = t2.id
where field2 = 'test' and t2.id is null

"NOTHING is more important in a database than integrity." ESquared

 

[kskinne]

Thank you, I will give this a try - the reason I posted this is I have a front end program that I wrote in VB that accesses data from my database, and I have a login screen that authenticates the user before allowing them access to the program.

What I plan to do now is keep a record of the last four passwords that they have used, and prevent them from setting a new password if it matches one of the four previously used ones. So whenever someone sets a new password, I will use a query to have it check this table to verify that it is doesn't match the four prior passwords. If it does not match, then it will change their password and add this new password to the password history table.

Now that I think about it though, I can probably accomplish the same thing by comparing the new password to the four most recent passwords, without actually deleting the older ones, I'll use something similar to the select statement you have shared with me, minus the part where it would delete any records, to authenticate the new password.

Thanks for your help,
Kevin