how to get the last record from the database?

hi friends,

i tried to get the last record in a specified column from a database. but it fails.

for example, i have a column name as "empid" in the table name "detail". in that, the records are in the form of "Emp1, Emp2, Emp3, .........Emp12."

i used this query --> select top 1 empid from detail order by empid desc

it is showing only Emp9.

it should show Emp12.

but the id's are showing in ascending order(Emp1,2,3,.....12) when i used this query --> select empid from detail

i've a gridview to show all Employee id's with paging(1-10 per page). So Emp12 would be showed in the second page. how to get that value "Emp12" from that gridview?

but i need a SQL query rather than this method (getting from gridview).

its urgent. Plz...................

since you are sorting a text column, you will not get a proper numerical sort. the correct text sort sequence would be

Emp1
Emp11
Emp2
Emp21
...
Emp9

in order to approach a numerical sort with text data, you need to store leading zeros with the numeric portion of your text

Emp000001
Emp000002
Emp000009
Emp000011
Emp000021

I would recommend that your seperate out the numerical data though and store it in a column with a numeric datatype.

|||

Uuh!.......... i stored Zeroes........no use............still it is showing "Emp09".............

suggest me another method............

|||

Although frankly speaking I didn't like this approach of storing string int mixed values and then trying it to sort it on the int part only, I thought to create at least something to get out of this kind of problem. I eventually came up with creating a UDF. The UDF code and sample code to test the function is as below. I hope you'll like it, but my advice is to separate out the string and int part, this will help you in days to come. Anyways, below is the code.

--- My functioncreate function ParseInt(@.valuevarchar(8000))returnsbigintasbegindeclare @.char varchar(1)declare @.lengthintdeclare @.counterintdeclare @.lastIndexintdeclare @.varIntvarchar(100)set @.varInt =''set @.counter = 1set @.length =len ( @.value )set @.lastIndex = 0while @.counter <= @.lengthbeginif ( ( @.lastIndex = 0or @.lastIndex + 1 = @.counter )and ( ascii (substring ( @.value , @.counter , 1 ) )between 48and 57 ) )beginset @.varInt = @.varInt +substring ( @.value , @.counter , 1 )set @.lastIndex = @.counterendset @.counter = @.counter + 1endreturncast ( @.varIntas bigint )endGO--- Sample Data to test the function

create table tbl(empvarchar(50))declare @.iintset @.i = 1while @.i <= 30begininsert tbl ( emp )select'empid' +cast ( @.ias varchar)set @.i = @.i + 1endGOselect * , dbo.ParseInt ( emp )from tblorder by dbo.ParseInt ( emp )desc

Hope this will help.

|||

Hi,

To make things simple, just add an identity column (eg: ID) to your table Detail and then fire the query

select top 1 * from Detail order by ID desc

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Hi,

In case you are want to know how to add an identity column to an existing table :

alter table Detail
add ID int IDENTITY(1,1) not null

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Hey friends,

no one works which is provided by you.................anyway i found a solution (not for sorting)........... thank u friends, for ur all replies............

how to get IDENTITY_INSERT Incriment Primary Key ID roll back when the application fails.

My question is how to get IDENTITY_INSERT Incriment Primary Key ID roll back when the application fails.

Using TransactionScope with single connection in DataObject. I am trying to insert row in two dataTable using its own tableAdapter (two tableAdapter).

I have Product table with ProductID primary key with incriment identity. and that ProductID is used to insert row in ProductHistory Table. Lets say Product table has the last ProductID=8 (8 rows) and the next ProductID will be 9.

When I insert row in both table and if the second table insert fails both gets roll back (which is good). but when I insert again another time the Product ID=10 not 9. Is there any way to roll back the ProductID in Product table so when i insert next time it has incriment number instead of gap.

That is the nature of IDENTITY column. The number once assigned to a row is gone whether the transaction succeeds or not. Even if the transaction succeeds, if you delete the row, the number is not re-assigned. Hopefully your application is based only on the PK-FK relationship and not on the serial order of the data.

|||

Thats what I thought but thought there might be a way I guess not, My table relation is based on PK-FK . It looks odd when some one is viewing Product table and has a gap in Product ID in between product (further someone might have question or confuse). I guess I have to manually create Product ID Primary Key data.

Thank for your answer

|||

Just out of curiosity, why is the ProductId important? ProductId should just be a PK to identify the product, it should not matter if there are gaps.

|||

For Developer standpoint it shouldnt matter at all, For other users, they would like to see incriment by 1. The product page displays Product ID, Product Name, Product Insert Date. So if they will see gap in Product ID inbetween they will have question and trust me they dont want that way, I have dealt in similar situation before.

|||

So why is productId even shown? Do users care?

|||

Oh Yes , that is how they identify the product. Product ID is more important then Product Name. Product ID is the key when communicating with anyone and used all over the page. Product come and go. It is very hard to keep up with their name. They find easy with Product ID

|||

rumax96:

Oh Yes , that is how they identify the product. Product ID is more important then Product Name. Product ID is the key when communicating with anyone and used all over the page. Product come and go. It is very hard to keep up with their name. They find easy with Product ID

Then a gap won't matter - unless your users remember that the product they want appeared 4 products further down a page than product 26, and therefore assume they want product 30. I know users can be extremely odd, but that seems to me to be pushing it a bit.

|||

Thanks Mike, I understand , From a developing perspective it doenst make sense wether there is a gap or not in Primary key field. The maing thing is user dont like gap in their ProductID and If that is the way the users wants then We have no choice. Like I said I have deal before in similar situation.