SQL query with order by in groups?

Question

I'm looking for a way to order my result in a correct way for a SQL Server 2019 query.

I have the table 'ItemCode' that is 4 numbers long and the first number stands for a group as part of the item code structure.

Now we also have 'ItemCode' with 5 numbers and I want those to be shown in the block of '1' if the first characters matches, same for 2 and so on.

Default list is something like this:

1010
2010
3010
10010
20010
30010

I want the output to be:

1010
10010
2010
20010
3010
30010

Grouped by the first number and then sorted ascending in that 'group' How can I make this work?

Answer 1

You are not specific on which database you are dealing with... Sql-Server, MySql, Postgres, etc. Also, what is the data type as stored.. integer, character. If integer, you need to convert to a string and get the leading character. But then need to consider the total length -- yes, you appear to only have 4 or 5 digits, but if any other future lengths, so you dont falsely get something like

1345
128
18294
223
21
289840

just because the first digit is pre-grouped consideration. I would think you are looking to have them by value low to high such as:

128
1345
18294
21
223
289840

Modified based on language (sql-server here) and data type column (assuming an integer column type, so I am pre-converting to a varchar)

select 
        left( convert( varchar(6), ItemCode), 1 ) LeadingDigit, 
        len( convert( varchar(6), ItemCode)) NumDigits, 
        convert( varchar(6), ItemCode) IntAsChar, 
        ItemCode
    from 
        YourTable
    order by
        1, 2, ItemCode

Here, the order by is based on the ordinal column of the query, just so you could run and see context of the output.

select 
        ItemCode 
    from 
        YourTable
    order by
        left( convert( varchar(6), ItemCode), 1 ), 
        len( convert( varchar(6), ItemCode)), 
        ItemCode

Now, if your column is already character, just strip out the conversion

select 
        ItemCode 
    from 
        YourTable
    order by
        left( ItemCode, 1 ), 
        len( ItemCode), 
        ItemCode

Answer 2

SELECT column

FROM table order by left(string(column),1)

Answer 3

`select 
i.codev
from 
    itemcode i 
order by
    left(i.codev,1)
    ,SUBSTRING(i.codev,3,1) desc 
;`

Column type: varchar