sorting orders

[ducky62]

i have 3 fields bay shelf slots and i'm using order by bay,shelf, slots and I get
bay shelf slots

103.2 1 13-16
103.2 1 1-4
103.2 1 17-18
103.2 2 11-14
103.2 2 1-2

and I want

103.2 1 1-4
103.2 1 13-16
103.2 1 17-18
103.2 2 1-2
103.2 2 11-14

is my order by wrong or is there something else I have to do?

 

[clapag22]

order by 1, 2, convert(int, left(slots, patindex('%-%', slots) - 1))

That should do it...

 

[ducky62]

convert(int, left(slots, patindex('%-%', slots) - 1))

what exactly does this do???

when i put it in it gives me an error...

 

[clapag22]

The '1' and '2' are just the ordinal positions of the columns. The rest is taking the numbers to the left of the hyphen and then converting them to numeric so it will order by correctly.

Sorry, I just gave you the actual order by statement. The entire query would be something like this:


select * from tester
order by 1, 2, convert(int, left(slots, patindex('%-%', slots) - 1))

 

[bperry]

Hi there,
clapag22's solution it interesting.... he/she is stripping of the leading values in the slots column up to (but not including) the hythen. It's quite interesting and neat.

Not sure why it doesn't work...probably some minor typo or syntax error. (By the way, it would have been more helpful, or easier to diagnose, if you had posted the actual error message instead of just "...it gives me an error."

I am wondering if there is an issue when 'slots' does not contain a '-' (if that is possible.) That might cause this solution to fail, I dunno.

Borrowing from clapag22's approach, here's a version that does seem to work well on my system...no syntax error, and seems to sort properly. Try it if you like. Pretty ugluy, though.
--------------------------
order by
left(slots,(case when (charindex('-', slots) - 1) = -1 then 0 else (charindex('-', slots) - 1) end))
---------------------------
And here's a slightly different version. Just as ugly.

order by
Cast(left(slots,(case when (charindex('-', slots) - 1) = -1 then 0 else (charindex('-', slots) - 1) end)) as int)
---------------------------

 

[clapag22]

Obviously you'll want to change the table name above as I pasted in my code using table name "tester"...

AND if you have other fields besides those three you'll need to change the ordinal number or just use the field names...

 

[bperry]

Oops. My apologies, I forgot that you also wanted to first sort by the first 2 columns, and THEN slots.

If you wanted to try my suggestion, then just insert the first two columns immediately after the 'Order BY:

Order by Bay, Shelf, LEFT(etc etc.....