Sorting Grid Right to left

Question

All,

We are having a problem with sorting the results of a database query.

The result of the query will be used to calculate discount to a customer. The table is then read from right to left.

However, the problem we are facing, is that we are unable to find a proper sort method to sort the results of this query.

The expected result looks like this, where the first line has the highest priority and the last line the lowest priority:

0   0   0   0   1
0   0   0   1   1
0   0   0   1   0
0   0   1   1   1
0   0   1   0   1
0   0   1   1   0
0   0   1   0   0
0   1   1   1   1
0   1   0   1   1
0   1   1   0   1
0   1   0   0   1
0   1   1   1   0
0   1   0   1   0
0   1   1   0   0
0   1   0   0   0
1   1   1   1   1
1   0   1   1   1
1   1   0   1   1
1   0   0   1   1
1   1   1   0   1
1   0   1   0   1
1   1   0   0   1
1   0   0   0   1
1   1   1   1   0
1   0   1   1   0
1   1   0   1   0
1   0   0   1   0
1   1   1   0   0
1   0   1   0   0
1   1   0   0   0
1   0   0   0   0

Hope anyone has a solution for sorting this result list.

If posible preferable in SQL.

Kind regards, Pieter Jong

Answer 1

I think you mixed up two lines, 1 1 1 0 1 and 1 0 0 1 1? If so, the comparison rule is: Find the first 1 from the left in either sequence. If the other sequence has a 0 there, it has higher priority; otherwise compare the sequences from the right.

Stated another way: Consider the sequences as binary numbers without leading zeros; shorter numbers have higher priority; if two numbers have equal length, reverse them, and the greater one has priority.

Stated yet another way: Read the sequences as binary numbers from right to left, then sort them first according to the number of factors of $2$ they contain and then according to the numbers themselves.

Stated yet another way: Read the sequences as binary numbers from right to left, write them as $2^km$ and sort the pairs $(k,m)$ in lexicographical order.

Answer 2

For all that want to see the query I have used.

The query will calculate all values, reversed, as Binary, as mentioned by 'joriki'.

After that, I have converted the columns to a string, eg 001101, then made a number out of this and measured the length. This length will then be used to add a value to allow sorting.

SELECT  Discounts_id
                , CONVERT
                (
                    BIGINT, 
                    (
                        c01 * 1 +
                        c02 * 2 +
                        c03 * 4 +
                        c04 * 8 +
                        c05 * 16 +
                        c06 * 32 +
                        c07 * 64 +
                        c08 * 128 +
                        c09 * 256 +
                        c10 * 512 +
                        c11 * 1024
                    ) 
                )
                +
                POWER
                (
                    CONVERT(BIGINT, 10), 
                    12 -    LEN
                            (
                                CAST
                                (
                                    CAST
                                    (
                                        (
                                            CONVERT(NVARCHAR, c01) +
                                            CONVERT(NVARCHAR, c02) +
                                            CONVERT(NVARCHAR, c03) +
                                            CONVERT(NVARCHAR, c04) +
                                            CONVERT(NVARCHAR, c05) +
                                            CONVERT(NVARCHAR, c06) +
                                            CONVERT(NVARCHAR, c07) +
                                            CONVERT(NVARCHAR, c08) +
                                            CONVERT(NVARCHAR, c09) +
                                            CONVERT(NVARCHAR, c10) +
                                            CONVERT(NVARCHAR, c11)
                                        ) AS INT
                                    ) AS NVARCHAR
                                )
                            )
                ) AS Counter_value      
                , Discount_percentage
                , Net_price 
                , Date_from
                , Date_until
        FROM    @TempTable

Hope this will help people with similar problems.

Kind regards,

Pieter Jong