the top 10

[David Nelson]

I saw it was added new in 13 but I do not remember its name so I can not find it to read up on how to do it.  It was something new added to SQL so we could ask for only the top x records.  What is it called?  Thank you for reading.

[Ocean West]

ExecuteSQL ( sqlQuery; fieldSeparator; rowSeparator {;arguments...} )

here is a great resource for discovering how to implement it.

http://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

offset and fetch is what you need

http://help.filemaker.com/app/answers/detail/a_id/12061/~/specifying-offset-and-fetch-first-clauses-in-filemaker-pro-using-the-executesql

Edited May 11, 2015 by Ocean West

[David Nelson]

That was perfect.  Thank you very much.

[sal88]

Has anyone had a problem using fetch with arguments (i.e. substituting the value with a '?')?

e.g.

executesql ("

SELECT * FROM t1 FETCH FIRST ?ROWS ONLY

";"";"";settings::global_row_specify)

does not work for me. But this does:

 

executesql ("

SELECT * FROM t1 FETCH FIRST " & settings::global_row_specify & " ROWS ONLY

";"";"";"")

 

Edited August 20, 2015 by sal88

[eos]

Has anyone had a problem using fetch with arguments (i.e. substituting the value with a '?')?

That does not work; but you can use

ExecuteSQL ( "
  SELECT *
  FROM t1
  FETCH FIRST " & settings::global_row_specify " ROWS ONLY
  " ; "" ; "" 
)

[sal88]

Perfect, thanks eos!