Thread: Problems with "order by clause"
Hello to everybody,
I have a little problem concerning the "ORDER BY" clause.
Consider a table "test" having one text field "val" with the following
entries:
tralala
Tralala
tralal
Now, I want to execute the following query via JDBC:
SELECT * FROM test ORDER BY val DESC;
When using JDBC with Oracle or Sapdb I get the following result:
tralala
tralal
Tralala
(correct lexicographic order as expected by Java)
Now, using PostgreSQL (7.1.3 with JDBC driver 7.2) the result is:
Tralala
tralala
tralal
(lexicographic order as implemented in PostgreSQL)
Any ideas what's going on there?
Thanks in advance,
-- Erik Behrends
----------------------------------------------
Inxnet GmbH
intelligent software solutions
tel. 0761 / 296 979-0
http://www.inxnet.de
----------------------------------------------
P.S.: Versenden personalisierter E-Mails - mit
inxmail ein Kinderspiel! http://www.inxmail.de
Erik, This isn't really a jdbc question, problably should be addressed on pgsql-general, or pgsql-sql, however I will give it a try. On my system I actually see the same results as Oracle and Sapdb and not the results you are reporting for postgres. This would lead me to believe that the behavior you are seeing is correct behavior but that you are running postgres with a locale that is different than mine. Sort order is determined by the locale that you are running under. My database is installed with a C locale (i.e. binary sort order) as opposed to a language/territory specific locale that would produce a different ordering. thanks, --Barry Erik Behrends wrote: > Hello to everybody, > > I have a little problem concerning the "ORDER BY" clause. > Consider a table "test" having one text field "val" with the following > entries: > > tralala > Tralala > tralal > > Now, I want to execute the following query via JDBC: > SELECT * FROM test ORDER BY val DESC; > When using JDBC with Oracle or Sapdb I get the following result: > > tralala > tralal > Tralala > > (correct lexicographic order as expected by Java) > > Now, using PostgreSQL (7.1.3 with JDBC driver 7.2) the result is: > > Tralala > tralala > tralal > > (lexicographic order as implemented in PostgreSQL) > > Any ideas what's going on there? > Thanks in advance, > > -- Erik Behrends > ---------------------------------------------- > Inxnet GmbH > intelligent software solutions > tel. 0761 / 296 979-0 > http://www.inxnet.de > ---------------------------------------------- > P.S.: Versenden personalisierter E-Mails - mit > inxmail ein Kinderspiel! http://www.inxmail.de > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
Hi Barry, I forgot to point out that the oracle and sapdb results I got via JDBC differed from the results I got directly in SQL-Plus resp. SQL-Studio: Tralala tralala tralal This led me to the assumption, that JDBC is doing some kind of formatting or something (?). To speak clearly: The results I get in a db console under oracle, sapdb and PostgreSQL are all the same, while the JDBC results differ in the way described below. This is what seems really weird to me... Looking forward to any further suggestions, Erik Barry Lind wrote: > Erik, > > This isn't really a jdbc question, problably should be addressed on > pgsql-general, or pgsql-sql, however I will give it a try. On my > system I actually see the same results as Oracle and Sapdb and not the > results you are reporting for postgres. This would lead me to believe > that the behavior you are seeing is correct behavior but that you are > running postgres with a locale that is different than mine. Sort > order is determined by the locale that you are running under. My > database is installed with a C locale (i.e. binary sort order) as > opposed to a language/territory specific locale that would produce a > different ordering. > > thanks, > --Barry > > > Erik Behrends wrote: > >> Hello to everybody, >> >> I have a little problem concerning the "ORDER BY" clause. >> Consider a table "test" having one text field "val" with the following >> entries: >> >> tralala >> Tralala >> tralal >> >> Now, I want to execute the following query via JDBC: >> SELECT * FROM test ORDER BY val DESC; >> When using JDBC with Oracle or Sapdb I get the following result: >> >> tralala >> tralal >> Tralala >> >> (correct lexicographic order as expected by Java) >> >> Now, using PostgreSQL (7.1.3 with JDBC driver 7.2) the result is: >> >> Tralala >> tralala >> tralal >> >> (lexicographic order as implemented in PostgreSQL) >> >> Any ideas what's going on there? >> Thanks in advance, >> >> -- Erik Behrends >> ---------------------------------------------- >> Inxnet GmbH >> intelligent software solutions >> tel. 0761 / 296 979-0 >> http://www.inxnet.de >> ---------------------------------------------- >> P.S.: Versenden personalisierter E-Mails - mit >> inxmail ein Kinderspiel! http://www.inxmail.de >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 5: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/users-lounge/docs/faq.html >> > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > > -- ---------------------------------------------- Inxnet GmbH intelligent software solutions tel. 0761 / 296 979-0 http://www.inxnet.de ---------------------------------------------- P.S.: Versenden personalisierter E-Mails - mit inxmail ein Kinderspiel! http://www.inxmail.de
Erik, I finally had a few minutes to try to reproduce you problem. However I do not see the same behavior as you. I tested on 7.2.1 with the 7.2.1 jdbc driver on a database created in the C locale. I see the same order in the output under jdbc as I do in psql. tralala tralal Tralala thanks, --Barry Erik Behrends wrote: > Hi Barry, > > I forgot to point out that the oracle and sapdb results I got via JDBC > differed from > the results I got directly in SQL-Plus resp. SQL-Studio: > > Tralala > tralala > tralal > > This led me to the assumption, that JDBC is doing some kind of > formatting or something (?). > To speak clearly: > The results I get in a db console under oracle, sapdb and PostgreSQL are > all > the same, while the JDBC results differ in the way described below. This > is what seems > really weird to me... > > Looking forward to any further suggestions, > > Erik > > Barry Lind wrote: > >> Erik, >> >> This isn't really a jdbc question, problably should be addressed on >> pgsql-general, or pgsql-sql, however I will give it a try. On my >> system I actually see the same results as Oracle and Sapdb and not the >> results you are reporting for postgres. This would lead me to believe >> that the behavior you are seeing is correct behavior but that you are >> running postgres with a locale that is different than mine. Sort >> order is determined by the locale that you are running under. My >> database is installed with a C locale (i.e. binary sort order) as >> opposed to a language/territory specific locale that would produce a >> different ordering. >> >> thanks, >> --Barry >> >> >> Erik Behrends wrote: >> >>> Hello to everybody, >>> >>> I have a little problem concerning the "ORDER BY" clause. >>> Consider a table "test" having one text field "val" with the following >>> entries: >>> >>> tralala >>> Tralala >>> tralal >>> >>> Now, I want to execute the following query via JDBC: >>> SELECT * FROM test ORDER BY val DESC; >>> When using JDBC with Oracle or Sapdb I get the following result: >>> >>> tralala >>> tralal >>> Tralala >>> >>> (correct lexicographic order as expected by Java) >>> >>> Now, using PostgreSQL (7.1.3 with JDBC driver 7.2) the result is: >>> >>> Tralala >>> tralala >>> tralal >>> >>> (lexicographic order as implemented in PostgreSQL) >>> >>> Any ideas what's going on there? >>> Thanks in advance, >>> >>> -- Erik Behrends >>> ---------------------------------------------- >>> Inxnet GmbH >>> intelligent software solutions >>> tel. 0761 / 296 979-0 >>> http://www.inxnet.de >>> ---------------------------------------------- >>> P.S.: Versenden personalisierter E-Mails - mit >>> inxmail ein Kinderspiel! http://www.inxmail.de >>> >>> >>> >>> ---------------------------(end of broadcast)--------------------------- >>> TIP 5: Have you checked our extensive FAQ? >>> >>> http://www.postgresql.org/users-lounge/docs/faq.html >>> >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 5: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/users-lounge/docs/faq.html >> >> > >