how to get order by with int with char values?

Question

Post reply

how to get order by with int with char values?

latitiacasta

SSCommitted

Points: 1982
More actions
June 1, 2011 at 11:16 pm

#389636

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

virgilrucsandescu SSCrazy Points: 2457 More actions · Answer 1

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?

latitiacasta SSCommitted Points: 1982 More actions · Answer 2

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

virgilrucsandescu SSCrazy Points: 2457 More actions · Answer 3

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

latitiacasta SSCommitted Points: 1982 More actions · Answer 4

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

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}

Richard Williams-416852 Old Hand Points: 374 More actions · Answer 6

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.

latitiacasta SSCommitted Points: 1982 More actions · Answer 7

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

Richard Williams-416852 Old Hand Points: 374 More actions · Answer 8

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.