I need Sql Query like this

Posted by Jayakumars under Regular Expressions on 7/14/2011 | Points: 10 | Views : 1466 | Status : [Member] [MVP] | Replies : 12
hi

Empid
--------
1
2
AED
3
20
EFR


i need out put like this how will achieve this
1
2
3
20
AED
EFR

Mark as Answer if its helpful to you


Responses

Posted by: Lakn2 on: 7/14/2011 [Member] Starter | Points: 25

Up
0
Down
select * from example Order By Case When IsNumeric(id) = 1   Then Right('0000000000000000000' + id, 20)  Else id End 


Thanks&Regards
LakshmiNarayana Nalluri.

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vishvvas on: 7/14/2011 [Member] [MVP] HonoraryPlatinum | Points: 25

Up
0
Down
SELECT
([EmpID]) FROM [TABLENAME] ORDER BY [EmpID]
This should solve the ordering as you need. When you say ID its generally the identity column (INT datatype) and in this example it seems alphanumeric.If its the candidate key for Employee entity, still having an identity column would be recommended which would have only integer values.
Hope this helps.

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 7/14/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi
Try this...
Select EmpID from TableName Order by CheckSum(EmpID)



Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakn2 on: 7/14/2011 [Member] Starter | Points: 25

Up
0
Down
hi PandianS,
your answer does n't meet his requirement. Check it once.

Thanks&Regards
LakshmiNarayana Nalluri.

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakn2 on: 7/14/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Vishvvas,

There is no rule to delcare id as integer we can declare as our wish if you know answer then post otherwise tell try this
except that don't say it is not possible.

Thanks&Regards
LakshmiNarayana Nalluri.

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 7/14/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
@Lakn2

The datatype of "Empid" is VARCHAR correct ?

The requirement is to bring the data in such order.. correct ?

If so, Then It should work...! If it's not working... Kindly excuse


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakn2 on: 7/14/2011 [Member] Starter | Points: 25

Up
0
Down
no i am not saying your query is wrong because i created one table for his reqirement like
id name id is nvarchar and name is char now i tried with query what i had posted that executes exactly what he wants.

but your query did n't give what he wants may be he may given varchar datatype to his field. it may work.

Thanks&Regards
LakshmiNarayana Nalluri.

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 7/14/2011 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
pandy and Lanks excellent thanks to all

hi lankans
your query here
select * from example Order By Case When IsNumeric(id) = 1 Then Right('0000000000000000000' + id, 20) Else id End

why include right '0000' like this i think before include employee id?

Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakn2 on: 7/14/2011 [Member] Starter | Points: 25

Up
0
Down
welcome

Thanks&Regards
LakshmiNarayana Nalluri.

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakn2 on: 7/15/2011 [Member] Starter | Points: 25

Up
0
Down
Hi
if you get answer please mark as answer .

Thanks&Regards
LakshmiNarayana Nalluri.

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vishvvas on: 7/15/2011 [Member] [MVP] HonoraryPlatinum | Points: 25

Up
0
Down
Lakn2,
Surprised to see your post. I have already answered the question and gave remarks about usage of identity column. There is not mention of any rule and this is relevant to quesiton itself, not needing separate post. Did you miss the answer part? And anyway, If it doesn't wotk, please excuse me because it hard to imagine that relevant recommendations are not welcome.
Thanks.

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakn2 on: 7/15/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Vishavvas,
do n't mis understand me what i am saying is you never say use interger only for id column because as per our requirement we can give datatype.
and i don't know if your code working for given requirement because i did n't check .i create one table what he wants and got result and i post it.
And one more thing i do n't recommend anybody i give ony sugessions. any way stop tihs discussion please.

Thanks&Regards
LakshmiNarayana Nalluri.

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response