Keep only 10 newest rows - 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

General SQL Server Forums

New to SQL Server Programming

Keep only 10 newest rows

Author

Topic

oap
Yak Posting Veteran

60 Posts

Posted - 2012-01-12 : 14:50:59

Hi there. I'd like to thank the forum for their replies so far, it has helped me greatly in my programming. There are some very gifted people here.

I would like to create a table that consists of a list of user actions performed by my users.

The table would be something like this:

-- MYTABLE --

USER, THEDATE, ACTION, FOLDER
ralph, 20/1/2011 1:00 AM, deleted pic, 233
ralph, 20/1/2011 1:01 AM, renamed pic, 233
tom, 20/1/2011 1:20 AM, viewed image, 200
nancy, 20/1/2011 2:00 AM, created folder, 233
tom, 24, 1, 2011 1:23 PM, changed description, 233

The idea is to log transactions performed on a variety of folders - the folders identified by numeric value (FOLDER). Now I would only like to retain the last 10 actions performed for any given FOLDER.

Anything after that would be purged from the table.

I cannot use:
SELECT * FROM MYTABLE ORDER BY THEDATE DESC
as I need to sort by FOLDER so that each folder tells you what user has made changes to it. Sorting by date would give me a variety of folders as a result.

I would require something like
SELECT * FROM MYTABLE ORDER BY THEDATE DESC GROUPBY FOLDER
and only the first 10 entries.

I know that there is a function to accept only the first xx rows, and to group by a given field, but I'm still left with how to remove all entries older than the 10th entry for a given FOLDER.

So...
1) What string would be the proper one to display a specific folder, from newest action to oldest action (DESC mydate), only the 10 newest records,
2) and then remove all entries beyond the first 10?

I could theoretically keep all of the actions in a table without purging any but with over 3000 folders, and hundreds of actions performed daily, the table would become very large.

Thank you for your help.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-12 : 15:02:24

Something like this might work for ya:

DELETE 
    T
FROM
    (
        SELECT 
            *, 
            ROW_NUMBER() OVER (PARTITION BY Folder ORDER BY ActionDate DESC) AS RowNum
        FROM
            MyTable
    ) AS T
WHERE
    RowNum > 10

oap
Yak Posting Veteran

60 Posts

Posted - 2012-01-12 : 17:39:21

Thank you so much.

Edit: It works great!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-13 : 04:21:51

maybe you should normalise a little.

you have a ACTION column which seems to contain string 'renamed pic' etc.

If you were to put those actions into their own table with a surrogate key and then replace the column with a foreign key then your fact rows will be a lot shorter. / db size would be less.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2012-01-13 : 05:28:15

Personally I would keep more than 10, but only display the most recent 10.

Someone always asks "How the hell did that happen?" and your history data would be handy for that.

SELECT TOP 10 Col1, Col2, ...
...
ORDER BY SomeColumn

will display what you want.

Then you can delete based on Age, rather than "last 10" perhaps, as people are likely to want to know "how the hell did that happen" within a reasonably recent timeframe. We tend to keep Audit Trail stuff for 3 months minimum, 6 months on tables where the rows don't change very often, and 14 months on things where we might want to compare "what the user did this time last year" - like order history

We tend to delete in two time periods:

If the retention period is, say, 3 months, we have an "extension" period of, say, 6 months and then we delete anything older than 6 months, and anything between 6-and-3 months where there is a newer change logged (so we always have the most recent change if there was a change made in the last 6 months, but if there were change(s) in the last 3 months then we have nothing more recent than 3 months). A consequence of this is that if there is no change in last 3 months then, at most, we will only retain one earlier record [i.e. the most recent] in the 3-to-6 month period

Thus we can say
"No, it hasn't changed in the last 3 months, the most recent change was 4.nnn months ago"
or
"There have not been ANY changes in the last 6 months"

Sorry, more complex than you asked for, but I mention it just in case it is food-for-thought.

Kristen
Test

22859 Posts

Posted - 2012-01-14 : 05:42:50

Hmmm ... does this:


If IsObject(objconn) Then objconn.close() ' Else MyDebugFunction("Unexpected CLOSE failulre at location 1234") End

solve it? But I can't immediately see why, in the example code, it would not still be open, and NEED closing, at that point.

Note that this sort of code:


SQL = "SELECT gnumpics FROM galleryindex where gsid= '" & fnum & "'"

is NOT a good idea. There are issues from SQL Injection, but also significant issues with performance & scalability, and also data typing. You should really use Parameterised Queries, or better still Stored procedures.

At its simplest you could do:


SQL = "EXEC sp_ExecuteSQL N'SELECT gnumpics FROM galleryindex where gsid= @fnum',
            N'@fnum int',
            @fnum='" & fnum & "'"

(line breaks just for illustration). SQL will cache the query plan for this query, and reuse it next time.

If you do:


SELECT gnumpics FROM galleryindex where gsid=1
SELECT gnumpics FROM galleryindex where gsid=2
SELECT gnumpics FROM galleryindex where gsid=3

SQL will make a new query plan each time, so no query-plan-reuse (unless you happen to query the exact same [gsid] number again).

Whereas the query:


SELECT gnumpics FROM galleryindex where gsid= @fnum

will be the same every time, and SQL will cache that (and make the parameter substitution separately from retrieving the query plan from Cache)

But ... parametrised queries would be better - where you pass each parameter to the query as a native variable from your language, and NOT by using string concatenation to make a SQL statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources