# Sorting Grid Right to left

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

-

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.

-
I think you are right about the 11101 and 10011, have changed it. – PJong Aug 9 '12 at 9:57
Thank you for your response! – PJong Aug 9 '12 at 11:27
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. – PJong Aug 9 '12 at 11:30

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

-