Professional Web Applications Themes

Storing only top 10 records? - Microsoft SQL / MS SQL Server

Platform SQL Server 2000/ASP.Net/C#/ADO.Net ------------------------------------------------------ My DataTable has following structure ItemTypeID int ItemID int AccessDate DateTime I want to store only the latest 10 items of each type. I want to delete Record 11 onwards whenever I insert a new Item. Can somebody suggest me how to do this? Becuase of performance reasons, I do not want to use temporary tables or cursors (This table would host such set of records for thousands of people) Thanks Manish...

  1. #1

    Default Storing only top 10 records?

    Platform SQL Server 2000/ASP.Net/C#/ADO.Net
    ------------------------------------------------------

    My DataTable has following structure

    ItemTypeID int
    ItemID int
    AccessDate DateTime

    I want to store only the latest 10 items of each type. I want to delete
    Record 11 onwards whenever I insert a new Item.

    Can somebody suggest me how to do this? Becuase of performance reasons, I do
    not want to use temporary tables or cursors (This table would host such set
    of records for thousands of people)

    Thanks

    Manish


    Manish Guest

  2. #2

    Default Re: Storing only top 10 records?

    Create a trigger on insert that deletes all records where accessdate not in
    (select top 10 accessdate from table order by accessdate). Keep in mind
    that if the 10th and 11th row share the same accessdate, they will both
    stay.




    "Manish Jain" <com> wrote in message
    news:phx.gbl... 
    do 
    set 


    Aaron Guest

  3. #3

    Default Re: Storing only top 10 records?

    Assuming that you want to keep the latest 10 rows based on Accessdate and
    assuming that Accessdate is unique and non-NULL:

    DELETE FROM Sometable
    WHERE accessdate <= ALL
    (SELECT TOP 10 accessdate
    FROM Sometable AS S
    WHERE itemtypeid = Sometable.itemtypeid
    ORDER BY accessdate DESC)

    If Accessdate is not unique then the above query will leave more than 10
    rows per itemtypeid. In that case you need to define the criteria for
    determing the "TOP 10". You could add the primary key after ORDER BY if that
    helps.
     

    I would suggest you implement this as a regular housekeeping task rather
    than do it with every INSERT. Putting the above code in a trigger will
    significantly impact performance for updates. Why do you want to do it with
    every INSERT?

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  4. #4

    Default Re: Storing only top 10 records?

    Without having actual table structure it would be difficult to give you
    exact solution
    check the following example. Im assuming you are checking the condition of
    latest record
    on the basis of accessdate field.
    Following example will keep latest 5 records
    you can make use of trigger.

    drop table test
    create table test(ItemTypeID int,ItemID int,AccessDate DateTime)

    insert into test values(1, 1, getdate())
    waitfor delay '00:00:02'
    go
    insert into test values(2, 1, getdate())
    waitfor delay '00:00:02'
    go
    insert into test values(3, 1, getdate())
    waitfor delay '00:00:02'
    go
    insert into test values(4, 1, getdate())
    waitfor delay '00:00:02'
    go
    insert into test values(5, 1, getdate())
    waitfor delay '00:00:02'
    go
    insert into test values(6, 1, getdate())
    waitfor delay '00:00:02'
    go
    insert into test values(7, 1, getdate())
    waitfor delay '00:00:02'
    go
    insert into test values(8, 1, getdate())
    waitfor delay '00:00:02'
    go
    --make use of following delete steatement inside the trigger.
    delete from test
    where not exists
    (select 1 from
    (select top 5 *
    from test
    order by accessdate desc )b
    where test.accessdate=b.accessdate)

    select * from test

    --
    -Vishal

    "Manish Jain" <com> wrote in message
    news:phx.gbl... 
    do 
    set 


    Vishal Guest

  5. #5

    Default Re: Storing only top 10 records?

    If I did get you right, you can do it with instead of trigger, something
    like in this example.

    Stjepan


    -- code --
    use tempdb

    if object_id('ItemAccess') is not null drop table ItemAccess
    go

    create table ItemAccess (
    ItemTypeID int,
    ItemID int,
    AccessDate DateTime,
    ItmOrder int
    )

    go

    create trigger tr_ItemAccess on ItemAccess
    instead of insert
    As
    begin

    if rowcount > 1 begin
    RAISERROR ('Cannot insert more than one row at the time!', 16,1)
    RETURN
    end

    declare maxid int
    declare itemOrder int


    select maxid = isnull(max(ItmOrder), 0) + 1 from ItemAccess

    select itemorder = a.ItmOrder
    from ItemAccess a
    join inserted i on a.ItemTypeID = i.ItemTypeID

    delete ItemAccess where ItmOrder = itemOrder

    update ItemAccess
    set ItmOrder = ItmOrder - 1
    where ItmOrder = isnull(itemOrder, 99999)

    update ItemAccess
    set ItmOrder = ItmOrder + 1
    where ItmOrder < isnull(itemOrder, 99999)

    delete ItemAccess where ItmOrder > 10

    insert into ItemAccess (ItmOrder, ItemTypeID, ItemID, AccessDate)
    select 1, ItemTypeID, ItemID, AccessDate
    from inserted



    end




    "Manish Jain" <com> wrote in message
    news:phx.gbl... 
    do 
    set 


    Stjepan Guest

Similar Threads

  1. Why not storing
    By ChintuBabu in forum Macromedia Flash Flashcom
    Replies: 1
    Last Post: June 30th, 03:59 PM
  2. Replies: 1
    Last Post: October 9th, 02:17 AM
  3. storing the value of PI
    By Ravi in forum Informix
    Replies: 13
    Last Post: December 23rd, 05:04 AM
  4. Many form records to many table records
    By Anne in forum Microsoft Access
    Replies: 4
    Last Post: September 3rd, 02:12 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139