Click here to Skip to main content
15,868,164 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
1. Here is the table structure

EmployeeID EmployeeName Rank
1 Deepak First
2 Kaushal Second
3 Pankaj First
4 Rahul Third
5 Riyaz Fourth
6 vimal Five
6 Suresh Fourth


I want to display it in ascending order as shown below :


EmployeeID EmployeeNAme Rank
1 Deepak First
3 Pankaj First
2 Kaushal Second
4 Rahul Third
5 Riyaz Fourth
6 Suresh Fourth
6 vimal Five

Is it possible to using sql server ?
Posted
Updated 21-Aug-12 23:41pm
v2

no, direct way to solve this.

two possible solutions are as below,
1. create new table that represent similar number of word
Rank   number
First  1
second 2
.
.
.

Now, join your table with above table using rank column & then order by 'Number' column

2. create function that converts word to number.
you will get number of similar word and then order by that number value

Happy Coding!
:)
 
Share this answer
 
v2
Comments
deepakdynamite 22-Aug-12 6:02am    
Appreciated !!! any other alternative ? as i don't know the limit
Aarti Meswania 22-Aug-12 6:08am    
As per my knowledge, no other way
because sql doesn't serve inbuilt functions for converting word(e.g 'First') to it's similar numerical value (e.g '1'). :)
I would suggest considering using for example a lookup table as Aarti suggested, but not to use the word as a joining column but the key of the lookup table. So both tables would have rank defined as a number and the lookup table would contain the description for the rank.

However, if the rank in your table is used to define the order of the rows for certain criteria, I would remove it completely. I'm suggesting this because if the reank is the order of the rows, you need to recalculate the ranks every time you insert, delete or update the records or the criteria changes. If the situation is like that, the rank is typically generated when a query is ran and it's not stored at all.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900