• Welcome, Guest. Please login.
 
September 17, 2019, 09:46:41 am

News:

Welcome to the SQLitening support forums!


array sqlitening sort

Started by Robert Sarrazin, August 01, 2009, 12:15:56 am

Previous topic - Next topic

Robert Sarrazin

Hi, this my problem.
I got client.sld with table cust

        REDIM lsaColsRows(0) AS STRING
        sSQL = bla bla bla
        slOpen sDatabase, "C"
        slSelAry (sSQL, lsaColsRows(), "N4")
        slClose
        y = UBOUND(lsaColsRows(2))

now I got exemple:
(1,x)  ;    (2,x)    ;      (3,x)         ;       (4,x)   ;   (......)
RowId;   comp    ;       buy x 1k    ;     contact ;   etc.

the lsaColsRows() 'work very well.

    1,3m,  12,  James, ......more data
    2,Philip,  4,  Frank, ......
    3,Advard,  21, Peter, .....

I want this result

    2,Philip,  4,  Frank, ......more data
    1,3m,  12,  James, ......
    3,Advard,  21, Peter, .....

I try all kind of sort,  to sort  the colum 3

ARRAY SORT lsaColsRows(3)
ARRAY SORT lsaColsRows(3) for 1
I build lsaBuy()   second array fill with 12,4,21  by the colum 3
ARRAY SORT lsaColsRows(), TAGARRAY lsaBuy()
ARRAY SORT lsaColsRows(0), TAGARRAY lsaBuy()
ARRAY SORT lsaColsRows(3), TAGARRAY lsaBuy()
no way all different result never good.

I try to resolved the problem by try
to sort with  slSelAry by option [,order by] .....(no result)

I find "JoeByrne" talking about:
You can (a) create an index on the column or
(b) use the 'SORT ON' command in your slSEL
but no exp. or detail. :-\
Just the "Actually, with Indexing, SORT ON, and the WHERE clause"
that all. ( I can't figure how is working)

but now someone
find a solution to sort a array by the column.
Thank hope the exp. is enough clear.
thank again. ::)

Rolf Brandt

Hello Robert,

QuotesSQL = bla bla bla


What does your SQL string actually look like? If you have defined the SQL query properly then there is no need for an array sort at all. You will get the data in the right order.

Rolf

I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Rolf Brandt

Hello Robert,

I assume that the database fields you want to display are:

RowId, comp, buy x 1k, contact, etc.

You want to sort by "buy x 1k", right?

Then your SQL query should be:

"SELECT RowId, * FROM Cust ORDER BY [buy x 1k]"

Like this you would have the data in the proper order and there is no need to sort.

Rolf
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Robert Sarrazin

Thanks.
the sSql = "SELECT RowId,  * FROM cust WHERE " + Seltxt + " = '0'" + "order by buy"

That is the real sSql the Seltxt is
Month=Month-1
or may look like this:
the sSql = "SELECT RowId,  * FROM cust WHERE " + "July" + " = '0'" + "order by buy"
the table contain the 12 month for the customer purchaces:
and to check all promotion we have to check if the customer is buying before or
buy this month due the promotion.

if july > 0  the customer is not select for the new promotion and the select
work great. (the problem is the sort the bying current month).

The real expression is:
if the customer last month buy nothing and how much is buying this month and
who is the best (DESCEND order).
???

After try I figure out how to use order by it is working(not the way we planing)

to follow my sample exp.
I got this: with the sSql select

    7,Kodak,  0,  Diane, ......more data
    4,Yahoo,  0,  Tom, ......
    6,Mash,  0,  Robert, .....
    5,Micro,  0,  Mark, ......
    1,3m,  12,  James, ......
    2,Philip,  4,  Frank, ......
    3,Advard,  21, Peter, .....

I got all the customer with no buying this month and at the end all
the one is buying this month.
The one with 0 is not a broblem to eleminated but
the 12, 4, 21 still not in sort

Kind a progress but still not the answer.
the Order By seem not to bad it bring all the 0 to the top.
All is there, just to figure out how to use 'array sort' the 3th column.
Thanks again.

Rolf Brandt

Let me see if I understood you properly:

Your table has fields January, February ... December. In these fields you save the sales of the customers - either in $, ?, or number of sales. The table has some other customer specific data like name address etc.

You want to find out which customers bought nothing in the previous month (July) but did sales in the current month (August).

This data should be sorted on the sales of the current month in descending order.

Am I right so far?

Let us assume the current month is August which makes the previous one July.
Then the following query should give you the desired results in the proper order:

sSql = "SELECT RowId,  * FROM cust WHERE July = '0' ORDER BY August [DESC]"

This should give you the following result:

    3,Advard,  21, Peter, .....more data
    1,3m,  12,  James, ......
    2,Philip,  4,  Frank, ......
    7,Kodak,  0,  Diane, ......
    4,Yahoo,  0,  Tom, ......
    6,Mash,  0,  Robert, .....
    5,Micro,  0,  Mark, ......


Does that meet your desired results?
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Robert Sarrazin

Work very well:
I still in shock after many hour follow the PB9 manual
instruction I am using wrong word.
this is your code
sSql = "SELECT RowId,  * FROM cust WHERE July = '0' ORDER BY August DESC"
compare with I use.
sSql = "SELECT RowId,  * FROM cust WHERE July = '0' ORDER BY August DESCEND"
I got no error compiling but when execute I got
1= near "DESCEND": syntax error
Now I realize the DESC is the code from sqlitening
compare to DESCEND is the code from PB9 for array sort.
  :) THANKS Rolf Thank's's's  :)  I never find this one. I was  realy stick with DESCEND

Rolf Brandt

August 03, 2009, 02:32:27 am #6 Last Edit: August 03, 2009, 02:34:26 am by Rolf Brandt
PB9 will never check SQL syntax since the SQL query is just a string you are handing over to the SQLite engine. The problem is that you are basically dealing with two different programming environments or two different languages which are sometimes similar in syntax.

Back in the 80ies and early 90ies I wrote programs in QuickBasic and dBase/Clipper. I had a similar problem because there syntax was similar, and sometimes I got mixed Basic with Clipper syntax.

Glad that I was able to help you.

Rolf
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu