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