June 1, 2011 at 11:16 pm
Hi all,
i have requirement in this i need order by on column which have combination of int + char.
values are:
1a
1b
1c
2a
2b
2c
i need result as
1a
2a
1b
2b
1c
2c
i really appreciate yours guidance
June 1, 2011 at 11:30 pm
SELECT ... ORDER BY REVERSE(field) should do the job if I got this right.
After all, even if the field is a combination of int+char, the result is a char..
Which is the range for the int?
June 1, 2011 at 11:37 pm
thanks for your reply
but here i have lit bit tricky one
i have values
1d
1y
2d
3d
2w
3y
2m
3m
i need result
1d
2d
3d
2w
2m
3m
2y
June 1, 2011 at 11:50 pm
ORDER BY
CASE
WHEN SUBSTRING(Field,2,1)='D' THEN 'A' + SUBSTRING(Field,1,1)
WHEN SUBSTRING(Field,2,1)='W' THEN 'B' + SUBSTRING(Field,1,1)
WHEN SUBSTRING(Field,2,1)='M' THEN 'C' + SUBSTRING(Field,1,1)
WHEN SUBSTRING(Field,2,1)='Y' THEN 'D' + SUBSTRING(Field,1,1)
END
but the performance of this will be miserable on large datasets ...
If these are real world tasks, I would suggest splitting that field in two ...
June 2, 2011 at 12:09 am
thanks for your reply
i know its affect performance,
this is real world task, and i am not able to split it.
client want as it is so i need to provide this way
June 2, 2011 at 12:40 am
try this:
DECLARE @Table TABLE
( Value CHAR(2))
INSERT INTO @Table VALUES
('1d'),
('1y'),
('2d'),
('3d'),
('2w'),
('3y'),
('2m'),
('3m')
SELECT Value
FROM @Table
ORDER BY RIGHT(Value,1) ,CAST ( LEFT(Value,1) AS INT)
{Edit: Edited the code as per the expected result}
June 2, 2011 at 1:07 am
Again, not the most performant thing in the work but a scalar function that takes your value as a parameter and returns a real number. This is especially useful if you are dealing with a set of data that has various formats e.g. 1.1, 1.1.1, 1a, 1b, 1XI etc etc - you have to know the complete subset of different possible formats and interpret them in your function to provide a number that will sort correctly e.g:
1.1.1 = 1.0101
1.2 = 1.02
1.11 = 1.11
1b = 1.02
etc
As long as the query you are sorting ensures that the set of data to be sorted is not to large this can a very useful technique.
If you have a defined format that never varies then use of a temporary table, a CTE or even a sub-query that creates a real number in one column and the original value in the other to which to join can be very fast.
June 2, 2011 at 1:09 am
here w will come after m,
i want w before m
so we need to put expression anyways its affect performance.
anyways thanx for your reply guys it will help me
June 2, 2011 at 1:21 am
Then use a function and encode the bizarre logic into it. Or create a permanent table in the database with the letter field and a numeric sort order field, then as long as there is some sort of consistency in your sorting logic you can use this table to join to or in your function.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply