Frustrating ORDER BY Problem

[OppThumb]

Hi,

I'm trying to use the ORDER BY option in both my Table design and in a
separate SQL query, and I'm getting the same puzzling result either
way. The two fields in the query below are text fields, nothing
special (Unicode compression is set to "Yes", but I don't see how that
could make a difference):

SELECT [Element name], [value]
FROM [analysis table]
WHERE [Element name] = "TEST-ELEMENT"
ORDER BY Trim([Value]);

Here's are the before-and-after results for my query. The "before"
represents the way it was loaded into the table, and when I open the
table, that's the way I see it:

Without ORDER BY:
Element name value
TEST-ELEMENT -00013700.00 THRU 00008669.06
TEST-ELEMENT 00008669.07 THRU 00031038.13
TEST-ELEMENT 00031038.14 THRU 00053407.20
TEST-ELEMENT 00053407.21 THRU 00075776.27
TEST-ELEMENT 00075776.28 THRU 00098145.34
TEST-ELEMENT 00098145.35 THRU 00120514.41
TEST-ELEMENT 00120514.42 THRU 00142883.48
TEST-ELEMENT 00142883.49 THRU 00165252.55
TEST-ELEMENT 00187621.63 THRU 00209990.70

With ORDER BY:
Element name Value
TEST-ELEMENT 00008669.07 THRU 00031038.13
TEST-ELEMENT -00013700.00 THRU 00008669.06
TEST-ELEMENT 00031038.14 THRU 00053407.20
TEST-ELEMENT 00053407.21 THRU 00075776.27
TEST-ELEMENT 00075776.28 THRU 00098145.34
TEST-ELEMENT 00098145.35 THRU 00120514.41
TEST-ELEMENT 00120514.42 THRU 00142883.48
TEST-ELEMENT 00142883.49 THRU 00165252.55
TEST-ELEMENT 00187621.63 THRU 00209990.70

Soooooo, why -- when I put the ORDER BY in my query -- does the
negative value appear in row #2? Can anybody suggest a way to put it
back in the right order? Based on this example it might appear that
the obvious thing is to take out the ORDER BY, but I can't always
count on them being in ascending order.

Any help will be appreciated.

Thanks,

John

 

[Guest]

Your ORDER BY is using Trim([Value]) -- Do you have leading spaces? That
could account for it.

 

[OppThumb]

I put the "Trim" on to be safe, but I re-ran the query with the Trim
on the field select as well, and it made no difference (same result).

I've looked at ASCII and Unicode character charts, and in both cases
the minus sign has a lower value than a zero. The Trim should have
eliminated any potential non-displaying characters, so... this is why
I said it was a frustrating problem.

Your ORDER BY is using Trim([Value]) -- Do you have leading spaces? That
could account for it.
--
KARL DEWEY
Build a little - Test a little

Hi,

I'm trying to use the ORDER BY option in both my Table design and in a
separate SQL query, and I'm getting the same puzzling result either
way. The two fields in the query below are text fields, nothing
special (Unicode compression is set to "Yes", but I don't see how that
could make a difference):

SELECT [Element name], [value]
FROM [analysis table]
WHERE [Element name] = "TEST-ELEMENT"
ORDER BY Trim([Value]);

Here's are the before-and-after results for my query. The "before"
represents the way it was loaded into the table, and when I open the
table, that's the way I see it:

Without ORDER BY:
Element name value
TEST-ELEMENT -00013700.00 THRU 00008669.06
TEST-ELEMENT 00008669.07 THRU 00031038.13
TEST-ELEMENT 00031038.14 THRU 00053407.20
TEST-ELEMENT 00053407.21 THRU 00075776.27
TEST-ELEMENT 00075776.28 THRU 00098145.34
TEST-ELEMENT 00098145.35 THRU 00120514.41
TEST-ELEMENT 00120514.42 THRU 00142883.48
TEST-ELEMENT 00142883.49 THRU 00165252.55
TEST-ELEMENT 00187621.63 THRU 00209990.70

With ORDER BY:
Element name Value
TEST-ELEMENT 00008669.07 THRU 00031038.13
TEST-ELEMENT -00013700.00 THRU 00008669.06
TEST-ELEMENT 00031038.14 THRU 00053407.20
TEST-ELEMENT 00053407.21 THRU 00075776.27
TEST-ELEMENT 00075776.28 THRU 00098145.34
TEST-ELEMENT 00098145.35 THRU 00120514.41
TEST-ELEMENT 00120514.42 THRU 00142883.48
TEST-ELEMENT 00142883.49 THRU 00165252.55
TEST-ELEMENT 00187621.63 THRU 00209990.70

Soooooo, why -- when I put the ORDER BY in my query -- does the
negative value appear in row #2? Can anybody suggest a way to put it
back in the right order? Based on this example it might appear that
the obvious thing is to take out the ORDER BY, but I can't always
count on them being in ascending order.

Any help will be appreciated.

John- Hide quoted text -

- Show quoted text -

 

[Michel Walsh]

ORDERing, under Win32, is *blind* to ' and - in the first positions of a
string (ie, Win32 behaves as if those characters were just NOT THERE, as far
as ordering is concerned... at least, on US based versions). You should be
able to observe the same behavior with ' such as in

Oa
O'conneil
Oz


The ordering will be as shown, as if the second line was Oconneil, without
', while ' occurs before the lower letters a to z, in the ANSI set. If the
' was visible, O'conneil would occur before Oa, which is not what people are
expecting (well, at least, accordingly to those who took the time to
implement that EXTRA to work in Win32, for ordering strings). Note that only
ordering, NOT comparison, is affected:


? "O'c" > "Oa"
False




You can explicitly test for - as first character, though:


ORDER BY "-" = Left(myString, 1) ASC, myString


if the first character is a "-", the test will be true, or -1, which occurs
before the cases where it is false, or 0.


Hoping it may help,
Vanderghast, Access MVP

Hi,

I'm trying to use the ORDER BY option in both my Table design and in a
separate SQL query, and I'm getting the same puzzling result either
way. The two fields in the query below are text fields, nothing
special (Unicode compression is set to "Yes", but I don't see how that
could make a difference):

SELECT [Element name], [value]
FROM [analysis table]
WHERE [Element name] = "TEST-ELEMENT"
ORDER BY Trim([Value]);

Here's are the before-and-after results for my query. The "before"
represents the way it was loaded into the table, and when I open the
table, that's the way I see it:

Without ORDER BY:
Element name value
TEST-ELEMENT -00013700.00 THRU 00008669.06
TEST-ELEMENT 00008669.07 THRU 00031038.13
TEST-ELEMENT 00031038.14 THRU 00053407.20
TEST-ELEMENT 00053407.21 THRU 00075776.27
TEST-ELEMENT 00075776.28 THRU 00098145.34
TEST-ELEMENT 00098145.35 THRU 00120514.41
TEST-ELEMENT 00120514.42 THRU 00142883.48
TEST-ELEMENT 00142883.49 THRU 00165252.55
TEST-ELEMENT 00187621.63 THRU 00209990.70

With ORDER BY:
Element name Value
TEST-ELEMENT 00008669.07 THRU 00031038.13
TEST-ELEMENT -00013700.00 THRU 00008669.06
TEST-ELEMENT 00031038.14 THRU 00053407.20
TEST-ELEMENT 00053407.21 THRU 00075776.27
TEST-ELEMENT 00075776.28 THRU 00098145.34
TEST-ELEMENT 00098145.35 THRU 00120514.41
TEST-ELEMENT 00120514.42 THRU 00142883.48
TEST-ELEMENT 00142883.49 THRU 00165252.55
TEST-ELEMENT 00187621.63 THRU 00209990.70

Soooooo, why -- when I put the ORDER BY in my query -- does the
negative value appear in row #2? Can anybody suggest a way to put it
back in the right order? Based on this example it might appear that
the obvious thing is to take out the ORDER BY, but I can't always
count on them being in ascending order.

Any help will be appreciated.

Thanks,

John

 

[OppThumb]

I appreciate the explanation, which is definitely good information to
know, but I'm not an Access expert. Are you saying I can put that test
in the ORDER BY clause? Or does the test get added to the SELECT
portion of the query?

ORDERing, under Win32, is *blind* to ' and - in the first positions of a
string (ie, Win32 behaves as if those characters were just NOT THERE, as far
as ordering is concerned... at least, on US based versions). You should be
able to observe the same behavior with ' such as in

Oa
O'conneil
Oz

The ordering will be as shown, as if the second line was Oconneil, without
', while ' occurs before the lower letters a to z, in the ANSI set. If the
' was visible, O'conneil would occur before Oa, which is not what people are
expecting (well, at least, accordingly to those who took the time to
implement that EXTRA to work in Win32, for ordering strings). Note that only
ordering, NOT comparison, is affected:

? "O'c" > "Oa"
False

You can explicitly test for - as first character, though:

ORDER BY "-" = Left(myString, 1) ASC, myString

if the first character is a "-", the test will be true, or -1, which occurs
before the cases where it is false, or 0.

Hoping it may help,
Vanderghast, Access MVP

Hi,

I'm trying to use the ORDER BY option in both my Table design and in a
separate SQL query, and I'm getting the same puzzling result either
way. The two fields in the query below are text fields, nothing
special (Unicode compression is set to "Yes", but I don't see how that
could make a difference):

SELECT [Element name], [value]
FROM [analysis table]
WHERE [Element name] = "TEST-ELEMENT"
ORDER BY Trim([Value]);

Here's are the before-and-after results for my query. The "before"
represents the way it was loaded into the table, and when I open the
table, that's the way I see it:

Without ORDER BY:
Element name value
TEST-ELEMENT -00013700.00 THRU 00008669.06
TEST-ELEMENT 00008669.07 THRU 00031038.13
TEST-ELEMENT 00031038.14 THRU 00053407.20
TEST-ELEMENT 00053407.21 THRU 00075776.27
TEST-ELEMENT 00075776.28 THRU 00098145.34
TEST-ELEMENT 00098145.35 THRU 00120514.41
TEST-ELEMENT 00120514.42 THRU 00142883.48
TEST-ELEMENT 00142883.49 THRU 00165252.55
TEST-ELEMENT 00187621.63 THRU 00209990.70

With ORDER BY:
Element name Value
TEST-ELEMENT 00008669.07 THRU 00031038.13
TEST-ELEMENT -00013700.00 THRU 00008669.06
TEST-ELEMENT 00031038.14 THRU 00053407.20
TEST-ELEMENT 00053407.21 THRU 00075776.27
TEST-ELEMENT 00075776.28 THRU 00098145.34
TEST-ELEMENT 00098145.35 THRU 00120514.41
TEST-ELEMENT 00120514.42 THRU 00142883.48
TEST-ELEMENT 00142883.49 THRU 00165252.55
TEST-ELEMENT 00187621.63 THRU 00209990.70

Soooooo, why -- when I put the ORDER BY in my query -- does the
negative value appear in row #2? Can anybody suggest a way to put it
back in the right order? Based on this example it might appear that
the obvious thing is to take out the ORDER BY, but I can't always
count on them being in ascending order.

Any help will be appreciated.

John- Hide quoted text -

- Show quoted text -

 

[John W. Vinson]

Soooooo, why -- when I put the ORDER BY in my query -- does the
negative value appear in row #2? Can anybody suggest a way to put it
back in the right order? Based on this example it might appear that
the obvious thing is to take out the ORDER BY, but I can't always
count on them being in ascending order.

Not at all sure why it's coming out in *that* order - but as a Text field it
will sort alphabetically, not numerically. Try

Order By Val([value])

This will return a numeric value (up to the blank) which should sort
correctly.

John W. Vinson [MVP]

 

[Michel Walsh]

You CAN order by on computed expression(s) that are not necessary selected
(not necessary in the SELECT list), yes.



ORDER BY "-" = Left(myString, 1) ASC, myString


order first on the first expression which returns either 0 either -1. Since
there is a second term, in the order by list, it is like

ORDER BY lastName, firstName

so that we will get:

Ah, Ozzy
Thumb, Opp
Thumb, Oz
Oz, Ozzy


Vanderghast, Access MVP

I appreciate the explanation, which is definitely good information to
know, but I'm not an Access expert. Are you saying I can put that test
in the ORDER BY clause? Or does the test get added to the SELECT
portion of the query?

ORDERing, under Win32, is *blind* to ' and - in the first positions of a
string (ie, Win32 behaves as if those characters were just NOT THERE, as
far
as ordering is concerned... at least, on US based versions). You should
be
able to observe the same behavior with ' such as in

Oa
O'conneil
Oz

The ordering will be as shown, as if the second line was Oconneil,
without
', while ' occurs before the lower letters a to z, in the ANSI set. If
the
' was visible, O'conneil would occur before Oa, which is not what people
are
expecting (well, at least, accordingly to those who took the time to
implement that EXTRA to work in Win32, for ordering strings). Note that
only
ordering, NOT comparison, is affected:

? "O'c" > "Oa"
False

You can explicitly test for - as first character, though:

ORDER BY "-" = Left(myString, 1) ASC, myString

if the first character is a "-", the test will be true, or -1, which
occurs
before the cases where it is false, or 0.

Hoping it may help,
Vanderghast, Access MVP

Hi,

I'm trying to use the ORDER BY option in both my Table design and in a
separate SQL query, and I'm getting the same puzzling result either
way. The two fields in the query below are text fields, nothing
special (Unicode compression is set to "Yes", but I don't see how that
could make a difference):

SELECT [Element name], [value]
FROM [analysis table]
WHERE [Element name] = "TEST-ELEMENT"
ORDER BY Trim([Value]);

Here's are the before-and-after results for my query. The "before"
represents the way it was loaded into the table, and when I open the
table, that's the way I see it:

Without ORDER BY:
Element name value
TEST-ELEMENT -00013700.00 THRU 00008669.06
TEST-ELEMENT 00008669.07 THRU 00031038.13
TEST-ELEMENT 00031038.14 THRU 00053407.20
TEST-ELEMENT 00053407.21 THRU 00075776.27
TEST-ELEMENT 00075776.28 THRU 00098145.34
TEST-ELEMENT 00098145.35 THRU 00120514.41
TEST-ELEMENT 00120514.42 THRU 00142883.48
TEST-ELEMENT 00142883.49 THRU 00165252.55
TEST-ELEMENT 00187621.63 THRU 00209990.70

With ORDER BY:
Element name Value
TEST-ELEMENT 00008669.07 THRU 00031038.13
TEST-ELEMENT -00013700.00 THRU 00008669.06
TEST-ELEMENT 00031038.14 THRU 00053407.20
TEST-ELEMENT 00053407.21 THRU 00075776.27
TEST-ELEMENT 00075776.28 THRU 00098145.34
TEST-ELEMENT 00098145.35 THRU 00120514.41
TEST-ELEMENT 00120514.42 THRU 00142883.48
TEST-ELEMENT 00142883.49 THRU 00165252.55
TEST-ELEMENT 00187621.63 THRU 00209990.70

Soooooo, why -- when I put the ORDER BY in my query -- does the
negative value appear in row #2? Can anybody suggest a way to put it
back in the right order? Based on this example it might appear that
the obvious thing is to take out the ORDER BY, but I can't always
count on them being in ascending order.

Any help will be appreciated.

John- Hide quoted text -

- Show quoted text -

 

[OppThumb]

That did the trick for the query I provided. One question, though --
it worked because there was only one negative value -- and
fortunately, that's all I expect to have in the great majority of my
values. However, if I have more than one, don't I run the risk of
having them show up out of order? For example, -1000 is less than
-100, but using this test, -100 will show first if both values are
present.

You CAN order by on computed expression(s) that are not necessary selected
(not necessary in the SELECT list), yes.

ORDER BY "-" = Left(myString, 1) ASC, myString

order first on the first expression which returns either 0 either -1. Since
there is a second term, in the order by list, it is like

ORDER BY lastName, firstName

so that we will get:

Ah, Ozzy
Thumb, Opp
Thumb, Oz
Oz, Ozzy

Vanderghast, Access MVP

I appreciate the explanation, which is definitely good information to
know, but I'm not an Access expert. Are you saying I can put that test
in the ORDER BY clause? Or does the test get added to the SELECT
portion of the query?

ORDERing, under Win32, is *blind* to ' and - in the first positions of a
string (ie, Win32 behaves as if those characters were just NOT THERE, as
far
as ordering is concerned... at least, on US based versions). You should
be
able to observe the same behavior with ' such as in
Oa
O'conneil
Oz
The ordering will be as shown, as if the second line was Oconneil,
without
', while ' occurs before the lower letters a to z, in the ANSI set. If
the
' was visible, O'conneil would occur before Oa, which is not what people
are
expecting (well, at least, accordingly to those who took the time to
implement that EXTRA to work in Win32, for ordering strings). Note that
only
ordering, NOT comparison, is affected:
? "O'c" > "Oa"
False
You can explicitly test for - as first character, though:
ORDER BY "-" = Left(myString, 1) ASC, myString
if the first character is a "-", the test will be true, or -1, which
occurs
before the cases where it is false, or 0.
Hoping it may help,
Vanderghast, Access MVP

Hi,
I'm trying to use the ORDER BY option in both my Table design and in a
separate SQL query, and I'm getting the same puzzling result either
way. The two fields in the query below are text fields, nothing
special (Unicode compression is set to "Yes", but I don't see how that
could make a difference):
SELECT [Element name], [value]
FROM [analysis table]
WHERE [Element name] = "TEST-ELEMENT"
ORDER BY Trim([Value]);
Here's are the before-and-after results for my query. The "before"
represents the way it was loaded into the table, and when I open the
table, that's the way I see it:
Without ORDER BY:
Element name value
TEST-ELEMENT -00013700.00 THRU 00008669.06
TEST-ELEMENT 00008669.07 THRU 00031038.13
TEST-ELEMENT 00031038.14 THRU 00053407.20
TEST-ELEMENT 00053407.21 THRU 00075776.27
TEST-ELEMENT 00075776.28 THRU 00098145.34
TEST-ELEMENT 00098145.35 THRU 00120514.41
TEST-ELEMENT 00120514.42 THRU 00142883.48
TEST-ELEMENT 00142883.49 THRU 00165252.55
TEST-ELEMENT 00187621.63 THRU 00209990.70
With ORDER BY:
Element name Value
TEST-ELEMENT 00008669.07 THRU 00031038.13
TEST-ELEMENT -00013700.00 THRU 00008669.06
TEST-ELEMENT 00031038.14 THRU 00053407.20
TEST-ELEMENT 00053407.21 THRU 00075776.27
TEST-ELEMENT 00075776.28 THRU 00098145.34
TEST-ELEMENT 00098145.35 THRU 00120514.41
TEST-ELEMENT 00120514.42 THRU 00142883.48
TEST-ELEMENT 00142883.49 THRU 00165252.55
TEST-ELEMENT 00187621.63 THRU 00209990.70
Soooooo, why -- when I put the ORDER BY in my query -- does the
negative value appear in row #2? Can anybody suggest a way to put it
back in the right order? Based on this example it might appear that
the obvious thing is to take out the ORDER BY, but I can't always
count on them being in ascending order.
Any help will be appreciated.
Thanks,
John- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Michel Walsh]

Yes, indeed, because you use string, and, Azzzz occurring BEFORE B, in the
dictionary, 10000 will occur before 2, when 10000, and 2, are strings.

Either FILL WITH ZEROS, so "00002" will appear before "10000",

same for "-0002" and "-1000" (but note that -1000 is less
than -2)


Either use VAL(yourString):


ORDER BY VAL(yourString) ASC, yourString


or just


ORDER BY VAL(yourString)


Hoping it may help,
Vanderghast, Access MVP

That did the trick for the query I provided. One question, though --
it worked because there was only one negative value -- and
fortunately, that's all I expect to have in the great majority of my
values. However, if I have more than one, don't I run the risk of
having them show up out of order? For example, -1000 is less than
-100, but using this test, -100 will show first if both values are
present.

You CAN order by on computed expression(s) that are not necessary
selected
(not necessary in the SELECT list), yes.

ORDER BY "-" = Left(myString, 1) ASC, myString

order first on the first expression which returns either 0 either -1.
Since
there is a second term, in the order by list, it is like

ORDER BY lastName, firstName

so that we will get:

Ah, Ozzy
Thumb, Opp
Thumb, Oz
Oz, Ozzy

Vanderghast, Access MVP

I appreciate the explanation, which is definitely good information to
know, but I'm not an Access expert. Are you saying I can put that test
in the ORDER BY clause? Or does the test get added to the SELECT
portion of the query?

On Apr 20, 2:07 pm, "Michel Walsh"
ORDERing, under Win32, is *blind* to ' and - in the first positions
of a
string (ie, Win32 behaves as if those characters were just NOT THERE,
as
far
as ordering is concerned... at least, on US based versions). You
should
be
able to observe the same behavior with ' such as in

The ordering will be as shown, as if the second line was Oconneil,
without
', while ' occurs before the lower letters a to z, in the ANSI set.
If
the
' was visible, O'conneil would occur before Oa, which is not what
people
are
expecting (well, at least, accordingly to those who took the time to
implement that EXTRA to work in Win32, for ordering strings). Note
that
only
ordering, NOT comparison, is affected:

? "O'c" > "Oa"
False

You can explicitly test for - as first character, though:

ORDER BY "-" = Left(myString, 1) ASC, myString

if the first character is a "-", the test will be true, or -1, which
occurs
before the cases where it is false, or 0.

Hoping it may help,
Vanderghast, Access MVP

I'm trying to use the ORDER BY option in both my Table design and in
a
separate SQL query, and I'm getting the same puzzling result either
way. The two fields in the query below are text fields, nothing
special (Unicode compression is set to "Yes", but I don't see how
that
could make a difference):

SELECT [Element name], [value]
FROM [analysis table]
WHERE [Element name] = "TEST-ELEMENT"
ORDER BY Trim([Value]);

Here's are the before-and-after results for my query. The "before"
represents the way it was loaded into the table, and when I open the
table, that's the way I see it:

Without ORDER BY:
Element name value
TEST-ELEMENT -00013700.00 THRU 00008669.06
TEST-ELEMENT 00008669.07 THRU 00031038.13
TEST-ELEMENT 00031038.14 THRU 00053407.20
TEST-ELEMENT 00053407.21 THRU 00075776.27
TEST-ELEMENT 00075776.28 THRU 00098145.34
TEST-ELEMENT 00098145.35 THRU 00120514.41
TEST-ELEMENT 00120514.42 THRU 00142883.48
TEST-ELEMENT 00142883.49 THRU 00165252.55
TEST-ELEMENT 00187621.63 THRU 00209990.70

With ORDER BY:
Element name Value
TEST-ELEMENT 00008669.07 THRU 00031038.13
TEST-ELEMENT -00013700.00 THRU 00008669.06
TEST-ELEMENT 00031038.14 THRU 00053407.20
TEST-ELEMENT 00053407.21 THRU 00075776.27
TEST-ELEMENT 00075776.28 THRU 00098145.34
TEST-ELEMENT 00098145.35 THRU 00120514.41
TEST-ELEMENT 00120514.42 THRU 00142883.48
TEST-ELEMENT 00142883.49 THRU 00165252.55
TEST-ELEMENT 00187621.63 THRU 00209990.70

Soooooo, why -- when I put the ORDER BY in my query -- does the
negative value appear in row #2? Can anybody suggest a way to put it
back in the right order? Based on this example it might appear that
the obvious thing is to take out the ORDER BY, but I can't always
count on them being in ascending order.

Any help will be appreciated.

John- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -