Click here to Skip to main content
15,943,082 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi friends,

I would like to get an SQL query for How to get a total records in a table except one record. That is suppose, I have 100 records but I want 99 only.

That except record may not be last. If that must be last tell me how to write for that also will you please give reply for that as early as possible.

Advanced thanks,
k sunitha

[orig. title]
i would like to get a Sql Query for total records of a table-1. That means all records except last record.for example,99 records is there but i want 98 only.
Updated 17-Feb-11 0:27am
Slacker007 17-Feb-11 6:28am    
Shortened title and corrected spelling.
Sandeep Mewara 17-Feb-11 6:42am    
There must be some logic based on which you want to leave a record. Whats that? Based on logic, query can be written.
Corporal Agarn 17-Feb-11 15:47pm    
Did you want to exclude one record or get a SUM from all but a certain record?

I did not test this, but this may work:

Select Top (Select Count(*) From Table - 1) From Table
Share this answer
It's bit unclear if you want just the total amount of records - 1 (which would be simply COUNT(*) - 1) or if you want to have the data from all records except one.

Let's say you need the records then you have to decide the correct condition for the WHERE clause. For example if you don't want to include the last row added and you have an IDENTITY field called ID in the table then the select could be something like:
FROM MyTable
            FROM MyTable)
Share this answer
If you want the total records in a table do:

select Count(*)as Total from [tablename]

If you want Total -1 do:

select Count(*)-1 as Total from [tablename]

If you want the total of records for a set criteria do:

select Count(*) as Total from [tablename] where [Field]=0 --whatever your condition may be

so in this case I am counting all records in the table having my field with a value of 0

Hope this solves your problem
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900