david yeah david yeah - 3 months ago 27
SQL Question

The reference to column "USER_COUNT" is not allowed in the argument of the TOP clause

I need some help on a problem.

I have a table like that :

CREATE TABLE [dbo].[USER_ACTIONS]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[USER_ID] [int] NOT NULL,
[ACTION_ID] [int] NOT NULL,
[DEVICE_ID] [tinyint] NOT NULL
[DATE] [datetime] NOT NULL,

PRIMARY KEY CLUSTERED ([ID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


I would like to keep the 10 newest records and
GROUP BY USER_ID, ACTION_ID AND DEVICE_ID
.

Here's what I tried :

IF OBJECT_ID('tempdb..#TempUsers') IS NOT NULL
DROP TABLE #TempUsers

SELECT USER_ID, COUNT(USER_ID) as USER_COUNT
INTO #TempUsers
FROM USER_ACTIONS
GROUP BY USER_ID, ACTION_ID,DEVICE_ID
HAVING COUNT(USER_ID) > 10
ORDER BY USER_COUNT

DELETE FROM USER_ACTIONS Actions
WHERE ID IN (SELECT TOP (USER_COUNT - 10) ID
FROM #TempUsers TMP
WHERE Actions.USER_ID = TMP.USER_ID
ORDER BY DATE ASC)


Unfortunately it doesn't work and I receive this message :


The reference to column "USER_COUNT" is not allowed in the argument of the TOP clause. Only references to columns at an outer scope or standalone expressions and subqueries are allowed here.


I would like to avoid loops for performance.

If someone has an idea I will be very grateful.

Thanks a lot!

Answer

You could just use ROW_NUMBER() and delete where it is higher than 10 to remove the oldest rows and leave the 10 newest for each combination

DELETE  t
FROM    (   SELECT  *, 
                    RowNumber = ROW_NUMBER() 
                                    OVER(PARTITION BY USER_ID, ACTION_ID,DEVICE_ID 
                                        ORDER BY DATE DESC) 
            FROM    USER_ACTIONS
        ) AS t
WHERE   t.RowNumber > 10;
Comments