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)
