how to delete /select last n rows in the table without sorting

Question

0.00/5 (No votes)

See more:

SQL-Server

hello experts,

how to fetch last n records from mytable.
any query for that,please inform immediatle

Posted 25-Oct-11 23:12pm

vinayfirstblock

Add a Solution

Comments

RaisKazi 26-Oct-11 5:21am

What do you mean by "sorting"? Does it mean "Order By"? What is a harm/disadvantage of "Order By" clause?

Amir Mahfoozi 26-Oct-11 5:22am

what is your criteria for "Last N" ? is the autoincrement key ok for you ?

3 solutions

Solution 3

Solution 2 is fine............

Posted 2-Nov-11 1:37am

Gopal Krishna Ranjan

Comments

DeepikaSrivastava 21-Nov-11 2:15am

Please mark it as answer...

Solution 2

To fetch last n records from mytable

SQL

Declare @n int
Set @n=5

select * from mytable
         where mytableid not in (
                                   select top ((select count(*) 
                                    from mytable) - @n ) mytableid
        from mytable

Posted 2-Nov-11 0:27am

DeepikaSrivastava

Updated 2-Nov-11 0:29am

v2

Comments

Member 12649531 10-Aug-16 8:16am

i have script like this in stored procedure
<pre lang="SQL">USE [Reporting_Tool]
GO
/****** Object: StoredProcedure [dbo].[asdf] Script Date: 2016-08-10 5:07:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[asdf]
As
begin
declare @rowcount as integer
declare @deletecount as integer
declare @Totalrows as integer
set @Totalrows=50
select @rowcount=count (*) from Table1;
IF (@rowcount > @Totalrows)
begin
select @deletecount=@rowcount-@Totalrows
print @Totalrows
print @rowcount
print @deletecount
delete top (@deletecount) from Table1
End
End
--select * from Table1
--EXEC asdf</pre>
but i want to delete old record not top records and how to fix number of rows into 50 that is first in first out automatically but not in statement scripts..
thanq..

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Espen Harlinn · Accepted Answer · 2011-10-25T23:27:00

Solution 1

Assuming ID is an identity column, I would do something like this:

SQL

SELECT TOP 10 
[ID] ,[Name]
  FROM [dbo].[myTable]
  ORDER BY ID DESC

As you are asking for "last" you are imposing order - the whole thing would be meaningless without ordering the result.

Best regards
Espen Harlinn

Posted 25-Oct-11 23:27pm

Espen Harlinn

Comments

RaisKazi 26-Oct-11 5:31am

A 5! Exactly, their is no point in avoiding "Order By".

Espen Harlinn 26-Oct-11 6:16am

Thanks RaisKazi!

sravani.v 2-Nov-11 6:30am

My 5!

Amir Mahfoozi 2-Nov-11 6:35am

+5

Espen Harlinn 2-Nov-11 6:36am

Thank you, Amir!