Issue with SELECT TOP where TOP dynamic

[tshad]

I am trying to delete oldest records after the last n records where n could be 5 or 6 (this would be passed in).

I tried using the SET ROWCOUNT @Top to set the row count but that doesn't work since it would set it for the whole query where I only want it for the subquery in my query.

I am trying to do something like:

SELECT *
FROM OldPassword
WHERE UserID = @UserId AND
OldPasswordId NOT IN (
SELECT TOP 4 OldPasswordId
FROM OldPassword
WHERE UserID = @UserId
ORDER BY InsertDate)

This works fine.

But I need the Top 4 to be dynamic and I don't want to write dynamic SQL.

Can this be done?

Thanks,

Tom

 

[gmmastros]

If you are using SQL2005 or newer, I would suggest you try this...

SELECT *
FROM OldPassword
WHERE UserID = @UserId AND
      OldPasswordId NOT IN (
            SELECT TOP [!](@NumberToKeep)[/!] OldPasswordId
            FROM OldPassword
            WHERE UserID = @UserId
                        ORDER BY InsertDate)

The parenthesis are important.

By the way... If you are using SQL2005 or newer, there are other ways to write the query, too.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tshad]

That was what I was looking for.

I tried it without the parens and got an error.

I am using 2008 - what would be the other way and is it more efficient.

Thanks,

Tom

 

[gmmastros]

It's hard to say if it will be more efficient because it will depend on the number of rows and what indexes you have.

However, If I were to write this query today, it would probably look like this:

;With CTE As (
Select OldPasswordId, Row_Number() Over(Order By InsertDate DESC) As RowNum
From   OldPassword
Where  UserId = @UserId
)
Delete OldPassword
From   OldPassword
       Inner Join CTE 
         On CTE.OldPasswordId = OldPassword.OldPasswordId
         And CTE.RowNum > @NumberToKeep

In my tests, the performance was very similar and the execution plans were also similar (but not the same). You would have to try both queries to determine which is more efficient.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tshad]

That looks pretty good.

But you have to be careful with CTEs, as I have found that in some cases, the CTE will take a long time to run where the derived table will run faster.

I have had derived tables (or subqueries) take about 3 seconds and the CTE take about a minute.

This is apparently a known issue.

Thanks,

Tom