How to get this out put

Hi i have a small question

when i use this SQL bellow

select EmpId,Type,Description from Employee
it will return me this out put

EmpId Type Description
01 Private Private company owner
02 Self Home based
03 Self Home based

Now my requirement is to get this out put

EmpId Type Description
01 Private
02 Self Home based
03 Self Home based

i need to get the description of the employee when employee type only 'Self'
rest of the employee Type should leave it blank ?
how do i do this task ?

regards
suis

Try the example below.

Chris

Code Snippet

DECLARE @.MyTable TABLE

(

[EmpID] CHAR(2),

[Type] CHAR(10),

[Description] VARCHAR(50)

)

INSERT INTO @.MyTable([EmpID], [Type], [Description])

SELECT '01', 'Private', 'Private company owner' UNION ALL

SELECT '02', 'Self', 'Home based' UNION ALL

SELECT '03', 'Self', 'Home based'

SELECT [EmpID],

[Type],

CASE WHEN [Type] = 'Self' THEN [Description]

ELSE ''

END AS [Description]

FROM @.MyTable

|||Hi chris
thank you very much for u r quick response,
i could manage to sort out my problem using u r comments,
thank you very much again for this forum
regards
suis

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