Sql sort script based on one column that has odd or even value

Question

I need help devising a sql script that will sort a bay item, based on the column value. If the level column value is even, then the bay ascends, but if the level column is odd, then bay descends. I have 12 levels, and 54 bays per level. So, shortened example (disregard dots after level number)

Result

Here is the script as it gives a conversion failed error when converting the varchar level to an int.

SELECT cast (SUBSTRING(cm.description, CHARINDEX('Bay:', cm.description) + 4, CHARINDEX(' ', cm.description, CHARINDEX('Bay:', cm.description)) - CHARINDEX('Bay:', cm.description) - 4) AS INT)      AS Bay,
       cast (SUBSTRING(cm.description, CHARINDEX('Level:', cm.description) + 6, CHARINDEX(' ', cm.description, CHARINDEX('Level:', cm.description)) - CHARINDEX('Level:', cm.description) - 6)AS INT) AS level
FROM   [Test].[Test].[Stations] AS cm
WHERE  ( cm.StationTypeId = '1' )
ORDER  BY CASE 'level' % 2
            WHEN 0 THEN 'bay'
            WHEN 1 THEN '-bay'
          END;

Here is what the description column looks like for each level:

Aisle:1 Bay:1 Level:1 Side:E

Column references should not be in single quotes. Also you can't use column aliases defined at the same level in expressions in `ORDER BY` (if you use them they must be on their own). So probably you need something like https://dbfiddle.uk/HlCA-J0x — Martin Smith, Mar 25 '23 at 19:26
Martin this does work; however, if I wanted to use left or full outer join, is that possible? The reason I ask is that this task is a subset of code from my entire sql script. Could you provide a version of this using a join statement? — Shuronda Hawkins, Mar 25 '23 at 23:10
I'm sure its possible but you should edit your question demonstrating what you actually need. I'm not going to guess it and just invent and solve a fictitious requirement — Martin Smith, Mar 26 '23 at 10:56
Hello, I tried to post the entire script but it's not allowing it. — Shuronda Hawkins, Mar 26 '23 at 13:54
Martin I've placed a screenshot of the entire code is why I asked. — Shuronda Hawkins, Mar 26 '23 at 15:02

shawnt00 · Answer 1 · 2023-03-25T06:13:51.380

1

order by
    level,
    case level % 2
        when 0 then -bay
        when 1 then  bay
    end

edited Mar 25 '23 at 06:13

answered Mar 25 '23 at 00:43

shawnt00

16,443
3
17
22

Is the -level a typo? – Shuronda Hawkins Mar 25 '23 at 00:56
No, it's a negative number. That's how you get it to reverse on odd vs even. – shawnt00 Mar 25 '23 at 01:00
I'll have to see what else may be wrong. It is giving an invalid column name when I add the negative. – Shuronda Hawkins Mar 25 '23 at 01:17
I had added in a comma between the two sort levels in case you did a copy and paste. No sure what error you'd see without it though. – shawnt00 Mar 25 '23 at 01:21
Shuronda, I may have had your sort orders reversed. Obviously the fundamentals of the answer are the same regardless. – shawnt00 Mar 25 '23 at 06:14
1

If it is giving an invalid column name when adding the negative it is probably a column alias for an expression in the SELECT – Martin Smith Mar 25 '23 at 07:39
Yes Martin, I had to use substring to pull the column and bay out of the string. Is there a way around it? – Shuronda Hawkins Mar 25 '23 at 13:13
I've added more details to the explanation. – Shuronda Hawkins Mar 25 '23 at 18:28

Charlieface · Accepted Answer · 2023-03-26T11:11:13.933

1

Assuming bay is actually a string, you can't use the other answer's trick of negating it.

Instead, use separate clauses.

you also cannot sort by a calcualtion on a field created in the SELECT. Instead put it into an APPLY subquery.

SELECT
  v.bay,
  v.level
FROM Test.Stations AS cm
CROSS APPLY (
    SELECT
      cast (SUBSTRING(cm.description, CHARINDEX('Bay:', cm.description) + 4, CHARINDEX(' ', cm.description, CHARINDEX('Bay:', cm.description)) - CHARINDEX('Bay:', cm.description) - 4) AS INT)      AS Bay,
      cast (SUBSTRING(cm.description, CHARINDEX('Level:', cm.description) + 6, CHARINDEX(' ', cm.description, CHARINDEX('Level:', cm.description)) - CHARINDEX('Level:', cm.description) - 6)AS INT) AS level
) AS v
WHERE  cm.StationTypeId = '1'
order by
    v.level,
    case when v.level % 2 = 1 then bay end asc,
    case when v.level % 2 = 0 then bay end desc;

edited Mar 26 '23 at 11:11

answered Mar 26 '23 at 05:04

Charlieface

52,284
6
19
43

It's shows invalid columns when using this. Is there something additional I should add to get it to recognize bay and level are columns? – Shuronda Hawkins Mar 26 '23 at 10:38
@ShurondaHawkins Fixed now, see edit – Charlieface Mar 26 '23 at 11:07
@MartinSmith good point didn't read the question properly. – Charlieface Mar 26 '23 at 11:07
Charlie there is more to the script in which I have about 5 join statements. Is there any way to devise the script so that it doesn't use cross apply? – Shuronda Hawkins Mar 26 '23 at 12:57
Why can't you use `CROSS APPLY` in such a script? It's pretty simple to use. See also https://stackoverflow.com/a/65818648/14868997 – Charlieface Mar 26 '23 at 12:58
I placed a screenshot of the entire code set. – Shuronda Hawkins Mar 26 '23 at 15:00
Screenshots are useless, but yeah you can just place the `CROSS APPLY` at the end just before `ORDER BY` – Charlieface Mar 26 '23 at 15:49
@MartinSmith adding it to the bottom of the screenshotted script causes it to produce two separate tables. I attempted to add level and bay in the main select, but it doesn't recognize them as columns or values. – Shuronda Hawkins Mar 27 '23 at 03:37
Like I keep saying, you need to add the `CROSS APPLY` to your existing script then it will recognize them. The screenshot is useless, I cannot copy/paste it as text. Please paste it in as text instead. – Charlieface Mar 27 '23 at 08:43
Thanks so much. I got it incorporated into my existing script. Works like a charm. – Shuronda Hawkins Apr 01 '23 at 17:37

Sql sort script based on one column that has odd or even value

2 Answers2