Board index » delphi » Delphi TQuery and Order By sql command

Delphi TQuery and Order By sql command

I working in Delphi 2, Using Paradox 5 tables and local sql. I am doing
a complex join (data from 2 tables) and I am using a TQuery Object. The
sql looks like the following;

select a.name, b.date from table1 a, table2 b
where b.id = a.id
order by name, date desc

The query works fine but the sort is a follows

Name = A thru Z then a thru z  (ascii)

I need to have it sorted

Name = A, a, thru Z, z

Does anyone know how to make this possible.

Thanks...

Sent via Deja.com http://www.deja.com/
Before you buy.

 

Re:Delphi TQuery and Order By sql command


Quote
On Wed, 02 Feb 2000 22:26:48 GMT, cdps_...@my-deja.com wrote:
>I working in Delphi 2, Using Paradox 5 tables and local sql. I am doing
>a complex join (data from 2 tables) and I am using a TQuery Object. The
>sql looks like the following;

>select a.name, b.date from table1 a, table2 b
>where b.id = a.id
>order by name, date desc

>The query works fine but the sort is a follows

>Name = A thru Z then a thru z  (ascii)

>I need to have it sorted

>Name = A, a, thru Z, z

>Does anyone know how to make this possible.

That is a proper ASCII sorting. The character "a" has an ASCII decimal
value of 97 and "Z" a value of 90. Because 90 is less than 97, "Z" comes
before "a".

One way to get what you want is to force the string column to be treated as
all upper case and sort on that.

  SELECT UPPER(a."name") AS U_Name, a."name", b."date"
  FROM table1 a, table2 b
  WHERE b.id = a.id
  ORDER BY U_Name, b."date"

Suppress the visibility of that calculated column by settings its Visible
property to True (the Visible property of TField, TColumn, or whatever is
most applicable to how you display the data). It is needed only in the SQL
statement for purposes of ordering the data.

Local SQL (the SQL implementation the BDE uses for dBASE, Paradox, and
FoxPro tables) is documented in the online help file LOCALSQL.HLP, found in
the main BDE directory. This help file is a language reference of the local
SQL implementation (subset) of SQL-92. The copy of this file that came with
BDE 4.x (and earlier versions) was seriously out of date and contained
little useful information. It has since been rewritten. It has been updated
again concurrent with C++Builder 5. Updated copies will have the topic
"VIEWs" in the index (and lack of this topic indicates a pre-update copy).
I can e-mail you a copy of the updated file if you need and desire it.

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steve Koterski              "Health nuts are going to feel stupid someday,
Felton, CA                  lying in hospitals dying of nothing."
                                                              -- Redd Foxx

Re:Delphi TQuery and Order By sql command


On Thu, 03 Feb 2000 16:51:14 GMT, koter...@NOSPAMgte.net (Steve

Quote
Koterski) wrote:
> It has been updated
>again concurrent with C++Builder 5. Updated copies will have the topic
>"VIEWs" in the index (and lack of this topic indicates a pre-update copy).
>I can e-mail you a copy of the updated file if you need and desire it.

Hi Steve,
Me too, please!
Thank you
Johan Smit
smi...@mweb.co,za

Other Threads