Re: Ordering text

I agree that is why I have concluded to do the calc in LC. However, I can’t store the balance one time in SQL as the balance value changes depending on which records are selected and the sort order(which may be by different columns). So it is not just a schema issue, it started out as a LC

Re: Ordering text

So this is a database schema issue. Typically what you will want to do with things like balances is to store the balance in a column as each value in the equation changes. You are asking your SQL server to do all the calculations for all your records all at once. If SLQ only has to calculate

Re: Ordering text

This actually works but is very slow (> 800ms) put merge("SELECT *,SUM (amount) OVER (ORDER BY recID) AS balance FROM myAccountName") into tSQL I have settled on my earlier suggestion Earlier suggestion results in 20ms performance. - I believe it’s sorted

Re: Ordering text

As more cobwebs and dust fall away . . . First impose a (temporary?) index column, idx, ordering the transactions, with a step of 1. And then you do something like UPDATE theTable SET tBal=tBal(idx -1) + charge WHERE idx > MIN(idx); ___

Re: Ordering text

Not enough info but something like: SELECT SUM(OLD -NEW) as balance FROM some_table WHERE need_to_calc IS true order by balance; (or order by whatever column. Not sure balance is what you are trying to sort by.) Bob S > On Mar 29, 2020, at 11:54 AM, doc hawk via use-livecode > wrote: > >

Re: Ordering text

I haven’t fully followed the queries that led to this, but would something like SELECT SUM(OLD -NEW) FROM some_table WHERE need_to_calc IS true; get you anywhere? I’m also thinking of a lag within the list, to assign them all at once, but the commands coming to memory are from statistical

Re: Ordering text

You took the SQL right out of my query. :-) Bob S On Mar 29, 2020, at 7:32 AM, Rick Harrison via use-livecode mailto:use-livecode@lists.runrev.com>> wrote: Hi Terry, Try having your database retrieval perform the sort and not LiveCode. Use the SQLite: ORDER BY clause. See if that gives you

Re: Ordering text

Hi Terry, Try having your database retrieval perform the sort and not LiveCode. Use the SQLite: ORDER BY clause. See if that gives you a faster result. Rick > On Mar 29, 2020, at 7:06 AM, Terence Heaford via use-livecode > wrote: > > I have approx. 7000 lines of tab delimited data. > >

Re: Ordering text

I believe it’s sorted (forgive the pun) As the data is stored in an SQLite db I have used the db to sum the values then worked from the top. Like below. This results in approx. 20ms for each sort direction. I have looked into a running total calc using SQLite but the solutions I found seemed

Re: Ordering text

Is there a key that you can use to sort?  May be faster to build it and then use the sort command at the end. Thanks, Brian On Mar 29, 2020, 7:16 AM -0400, Tore Nilsen via use-livecode , wrote: > I believe the reason for the delay comes from the fact that when sorted > descending, you put tRec

Re: Ordering text

I believe the reason for the delay comes from the fact that when sorted descending, you put tRec & CR before tCellData. Tp put something before a container is slower than putting something after a container. Regards Tore Nilsen > 29. mar. 2020 kl. 13:06 skrev Terence Heaford via use-livecode

Ordering text

I have approx. 7000 lines of tab delimited data. When I execute the code snippet below if the sort order is descending it takes approx 120 milliseconds otherwise it takes 20 milliseconds to process. Can someone explain why and perhaps come up with a faster solution. Thanks Terry ——