Resultset - Returning Last n Reocrds in Table

Question

Post reply

Resultset - Returning Last n Reocrds in Table

Cory Slaney

SSCrazy

Points: 2459
More actions
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.

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

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

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

Cory Slaney SSCrazy Points: 2459 More actions · Answer 3

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

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.

Kory Becker Hall of Fame Points: 3585 More actions · Answer 5

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.

Cory Slaney SSCrazy Points: 2459 More actions · Answer 6

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).

Farrell Keough SSCoach Points: 17414 More actions · Answer 7

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.

SuperDBA-207096 SSCrazy Eights Points: 8176 More actions · Answer 8

SuperDBA-207096

SSCrazy Eights

Points: 8176

October 28, 2005 at 8:57 am

#600939

Can you just add an identity column to the table?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

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

Cory Slaney SSCrazy Points: 2459 More actions · Answer 10

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

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

noeld SSC Guru Points: 96590 More actions · Answer 12

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