ExecuteSQL: How do I sort my columns by count value, but only display the value in the results?

[Jay86]

I have an ExecuteSQL question that I'm hoping you could help solve for me [FMP18A on MAC].

The best way for me to outline my question is to show the process I took to get to where I am currently.

[Example data only]

I have the following TABLE [Sales_Database] and COLUMN [FRUIT] setup, and my SQL calculation gives the following result:

------------------

ExecuteSQL (

"SELECT Fruit, COUNT(*)

FROM Sales_Database

GROUP by Fruit

ORDER by 2 DESC

"

; "" ; "")

 

Apple, 77

Oranges, 67

Grapes, 57

Pears, 47

Bananas, 37

------------------

I then broke it down further, wanting the results to only display the top most value (Apple). So, my SQL calculation and result was thus:

------------------

"SELECT Fruit, COUNT(*)

FROM Sales_Database

GROUP by Fruit

ORDER by 2 DESC

FETCH FIRST 1 ROWS ONLY

"

; "" ; "")

 

Apple, 77

------------------

 

So far, so good.

 

HOWEVER, for the purpose of using the result in a HIDE OBJECT WHEN calculation, I need the result to only show the word "Apple" no number attached.

 

Removing the COUNT(*) just results in the calculation ordering the values by the length of their letters.

 

I hope there's a solution to this problem, and thank you all in advance.

[Fitch]

Don't forget the other functions available to you. You could set the ExecuteSQL field separator to a carriage return, or take your result and use Substitute -- and then get the first value, so the whole thing would be:

GetValue( Substitute(result;", ";¶) ; 1 )

That said, I would highly advise you not to use ExecuteSQL in any hide condition, tooltip, conditional format, etc. In a hosted, multi-user file, an uncommitted record can cause the client to attempt to retrieve the entire database. Performance will suffer to say the least.

Welcome to the forums!

[Jay86]

Hi Fitch, thank you for your reply.

Would you mind elaborating a little on how I implement the "GetValue( Substitute(result;", ";¶) ; 1 )"

Does it goes at the end of the SQL query, or elsewhere?

[comment]

This is a cross-post:
https://community.claris.com/en/s/question/0D50H00007oQM1MSAW/executesql-how-do-i-sort-my-columns-by-count-value-but-only-display-the-value-in-the-results

The answers you were given there are practically the same as the one given above.

 

58 minutes ago, Fitch said:

I would highly advise you not to use ExecuteSQL ...

I agree. I would suggest a native alternative, but I am not sure what is the exact purpose of this exercise.

 

[Jay86]

33 minutes ago, comment said:

This is a cross-post:
https://community.claris.com/en/s/question/0D50H00007oQM1MSAW/executesql-how-do-i-sort-my-columns-by-count-value-but-only-display-the-value-in-the-results

The answers you were given there are practically the same as the one given above.

Apologies, I had no idea that both these websites were connected.

I'll continue this discussion in the other thread.

Appreciate all your help with this.

[comment]

41 minutes ago, Jay86 said:

I had no idea that both these websites were connected.

They are not, but some of us read both. You are welcome to post on both, but a heads up would be appreciated in order to save duplication of effort.