Need query help. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need query help.

Author

Topic

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-22 : 11:22:19

Is there a better way?

I have a password history table that functions as a fifo queue.


create table #hist (
    userid int not null,
    pword varchar(14) not null,
    dt smalldatetime not null,
    constraint pk primary key (userid,pword) )
go
insert #hist
select 1,'oldest','9/11/01' union
select 1,'middle','9/12/02' union
select 1,'newest',getdate()

insert #hist
select 2,'oldest','1/1/00' union
select 2,'newest',getdate()
go

I intend to only remember the last three passwords, so I want to pop (delete) the oldest record, if there are three. If there are {0,1,2} records, I don't want to delete anything. Here is the working query I have come up with..


delete h
from
    #hist h
where exists (
    select 1
    from
        #hist
    where
        h.userid = userid
    group by
        userid
    having
        count(*) = 3) and
    dt = (
    select
        min(dt)
    from
        #hist
    where
        h.userid = userid ) and
    userid = @userid
go

..but I am wondering if someone has a smarter way to write it so that I don't have to seek on pk 3 times...

<O>

dsdeming

479 Posts

Posted - 2002-07-22 : 13:25:18

I'm making 2 assumptions: that your history table is not a temp table, and that rows are inserted into the table one at a time rather that in large batches.

Wouldn't this be an ideal situation for a trigger? You could delete the oldest password before inserting the new one if there are already three in the table.

Alternatively, you could ensure that a user always has three passwords in the table by inserting two dummy rows ( with dt set to different values at some point in the past ) along with the user's initial password when the user record is first created. Then in subsequent inserts you don't have to test for the existence of three rows with a HAVING clause in order to determine whether or not you need to delete one. You will always delete the oldest.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources