Take the 2-minute tour ×
Mathematics Stack Exchange is a question and answer site for people studying math at any level and professionals in related fields. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers 2

up vote 0 down vote accepted

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.

share|improve this answer
    
I think you are right about the 11101 and 10011, have changed it. –  Pieter Jong Aug 9 '12 at 9:57
    
Thank you for your response! –  Pieter Jong Aug 9 '12 at 11:27
1  
I have tried it now using the Binary approach and using the shortest length as sort. For example, first I make a string of the values 00110 then convert them to a number, get the length of the number. I also reverse this string and make it read as binary with factor 2, this will result in a value 12. Then by sorting on the two I get the best matching result. –  Pieter Jong Aug 9 '12 at 11:30
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.