Post reply

Resultset - Returning Last n Reocrds in Table

  • October 27, 2005 at 9:59 am

    #90692

    Is there a way to select the last n records inserted into a table? I have a table with just a single column containing e-mail addresses and I would just like to return the last 30 entered. Is this possible? Is there a round-about way using count, top, or some other select arguement(s) to accomplish this? Any help would be greatly appreciated.

  • October 27, 2005 at 10:13 am

    #600647

    Well depends

    Do you have an indentiy column on your table?

    If so its easy.

    If not Not easy

    Select top 30 Email

    from MyTable

    Order by Identitycol desc

  • October 27, 2005 at 10:22 am

    #600655

    Assuming DateReceived column exists, then you could order by that as well.

  • October 27, 2005 at 10:35 am

    #600674

    Thanks all. Just a table with one column, 'email' .... no identity column.

  • October 27, 2005 at 11:17 am

    #600687

    Here is a clunky, (bulldozer :pinch approach: 

    CREATE TABLE #Email( EmailAddress varchar(10))

    INSERT INTO #Email( EmailAddress)

    SELECT ' 1'  UNION ALL

    SELECT ' 2'  UNION ALL

    SELECT ' 3'  UNION ALL

    SELECT ' 4'  UNION ALL

    SELECT ' 5'  UNION ALL

    SELECT ' 6'  UNION ALL

    SELECT ' 7'  UNION ALL

    SELECT ' 8'  UNION ALL

    SELECT ' 9'  UNION ALL

    SELECT '10' UNION ALL

    SELECT '11' UNION ALL

    SELECT '12' UNION ALL

    SELECT '13' UNION ALL

    SELECT '14' UNION ALL

    SELECT '15' UNION ALL

    SELECT '16' UNION ALL

    SELECT '17' UNION ALL

    SELECT '18' UNION ALL

    SELECT '19' UNION ALL

    SELECT '20' UNION ALL

    SELECT '21' UNION ALL

    SELECT '22' UNION ALL

    SELECT '23' UNION ALL

    SELECT '24' UNION ALL

    SELECT '25' UNION ALL

    SELECT '26' UNION ALL

    SELECT '27' UNION ALL

    SELECT '28' UNION ALL

    SELECT '29' UNION ALL

    SELECT '30' UNION ALL

    SELECT '31' UNION ALL

    SELECT '32' UNION ALL

    SELECT '33' UNION ALL

    SELECT '34' UNION ALL

    SELECT '35'

    DECLARE @Count integer,

                    @SQL varchar(2000)

    SET @Count =(( SELECT COUNT(*) FROM #Email) - 30)

    SET @SQL = 'SELECT EmailAddress FROM #Email WHERE EmailAddress NOT IN( ' + '

                         SELECT TOP ' + CONVERT( varchar, @Count) + ' EmailAddress FROM #Email) '

    PRINT @SQL

    EXECUTE( @SQL)

    DROP TABLE #Email

    I wasn't born stupid - I had to study.

  • October 27, 2005 at 11:50 am

    #600701

    Create a temp table with and identity column and the email adress select the data into the table and then use Ray M's approach.

    This may not work if the data get reorganized due to Clustered Index rebuilds.

  • October 27, 2005 at 12:31 pm

    #600711

    Thanks all. I will use the suggestions above, and yes, if you are wondering why someone would construct such a table, I didn't, I inherited it from some unknown 'deadhead' DBA (and I use that term very loosely).

  • October 27, 2005 at 2:42 pm

    #600759

    Please keep in mind that this is a crummy solution and does not deal with Ordering...  I would suggest you make a new table with a correct design and let your Developer's know...

    Good luck - we have all inherited crummy stuff before and it is a real pain...

     

    I wasn't born stupid - I had to study.

  • October 28, 2005 at 8:57 am

    #600939

    Can you just add an identity column to the table?

  • October 28, 2005 at 9:06 am

    #600942

    Doesn't solve the problem for the current e-mails.. but it would for the next.

  • October 28, 2005 at 12:28 pm

    #601050

    If I add an identity column to the current table, will the same order be retained. As of right now, order is important. If order was retaine then I would definitely go ahead and and add the new column.

  • October 28, 2005 at 12:30 pm

    #601051

    It should, but test this on a backup of the db just to be sure.

  • October 28, 2005 at 2:58 pm

    #601113

    if the table has only one column and you don't have any other indication unles the table is very very small as of this moment you can't tell what was entered before or after

    The addition of a column does NOT gurant any order you need to create a destination table with the identity in it and use a select ...order by xxx and only then you will be sure that the identity was assigned in the order you wanted.

    Cheers,

     


    * Noel

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply