Click here to Skip to main content
11,414,234 members (69,934 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server-2008
Hi all,

Iam working on SQLserver 2005 database using asp.net c#.

In my table i have 5 rows when i click on delete button it should delete first all rows except last row.

This table is incrementable, it will add rows.

This is my asp.net C# code.

SqlConnection conDelete = new SqlConnection(_connString);
            SqlCommand cmdDelete = new SqlCommand();
            cmdDelete.Connection = conDelete;
          
            cmdDelete.CommandText = "Delete from UserTable where Id in (select top 3 Id from UserTable ORDER by Id asc)";
            using (cmdDelete)
            {
                conDelete.Open();
 
                cmdDelete.ExecuteNonQuery();
                conDelete.Close();
 
            }


Please help, thanks in advance.
Posted 12-Jan-14 20:19pm
Edited 12-Jan-14 20:37pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

This will delete all the rows except the last row.
Delete from UserTable where Id not in (select Id from UserTable ORDER by Id desc)";
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

If you just want to delete all rows except the last one. You can use MAX(..) function.
Write a SQL like this, deletes all rows where Id is less than max.

DELETE FROM UserTable WHERE Id < (SELECT MAX(Id) FROM UserTable)
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 9,130
1 OriginalGriff 7,477
2 Maciej Los 3,710
3 Abhinav S 3,298
4 Peter Leow 3,084


Advertise | Privacy | Mobile
Web01 | 2.8.150427.2 | Last Updated 13 Jan 2014
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100