June 13, 2005 at 1:47 am
Hi all,
I want to know if there is any method through which we can sort the data in customized order. If there is any way then pls tell me.
Any help will be appreciated.
June 13, 2005 at 2:18 am
Yes , By using "Case When else end " u can do it
for example order by case when type = 'others' then 'zzzz' else type end .
Here order by is working on field "type" .
June 13, 2005 at 7:28 am
Amit's trick will certainly help you someday. But what do you mean by customized sort? What do you need exactly?
June 14, 2005 at 4:50 am
It is really a great idea. But What I need is different. Suppose I have records like:
B
A
F
D
E
C
I dont want to sort them in ascending or descending order. But in the order I want suppose I want them in order of :
B
F
E
C
D
A
Then What should I do?
Thanks in advance
June 14, 2005 at 5:02 am
and what order would you call that?
If you can't mathematically explain an order you can't do it via a set method. The rules you perscribe aren't logical, they're probably a business requirement (at a guess) and therefore you'll probably have to order your records via you application code or use a cursor and iterate inserting into a temporary table or table variable which has a sequential identifier (identity column) which you can then select from and order by, or you create multiple insert statement based on your order
IE:
insert into @mytable(col1,col2,etc...)
select col1,col2,etc... from table1 where myrule='B'
followed by an insert statement for each of the remaining ordering rules. Then select from the table ordering by your identity column.
regards,
Mark Baekdal
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change management for SQL Server
June 14, 2005 at 6:44 am
Another option would be to have another look up table on which you could join to, and then sort on a field of this table (which would be ordered in the same fashion you showed us).
June 15, 2005 at 2:44 am
Thanks Enthusiast,
I was really looking at the query like that. It really works.
June 15, 2005 at 8:20 am
BTW his name is Joe Celko, enthusiast is just related to his number of posts.
Just wanna add that the the longer the string in the charindex function, the slower this will run. Make sure it's not too long for nothing.
June 15, 2005 at 9:03 am
Enthusiastic may be way to mild to describe Joe when you hit one of his hot buttons. He does a great rant .
Mike
His books are great sources of information. (Just a quick plug for Joe's books)
June 15, 2005 at 9:11 am
Never thaught of that one .
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply