1 2 Previous Next 19 Replies Latest reply on Mar 5, 2011 5:17 PM by AllanCrump

    sql rowid

    AllanCrump

      Title

      sql rowid

      Post

      I am using the bundled Datadirect odbc drivers that came with FM. I have a database of 10,000 English words that my students use to lookup in our dictionary. I would like to randomly select a word from this database for them to practice, but I seem to have run into a syntax snag. Is it possible to fetch only ONE random record from a set of found records? I have tried every imaginable SELECT syntax I can, but not sure how to do this in filemaker sql terms:

      $db->Sql( "SELECT COUNT(*) as Cwds FROM ICST = Cwds returns the value of 7000 records in file, so I create a random number:

      $rndn = (any random number), i.e. 1203

      and I'd like to Fetchrow number 1203, is that even possible? I tried many variations like this one:

      SELECT * ROWID() as ROW,English FROM ICST WHERE NVA='N' AND ROW = $rndn

      but I am getting nowhere. Ideas?

        • 1. Re: sql rowid
          Sorbsbuster

          With only 10,000 words, why not pull them all back into FM and select the random one there?

          • 2. Re: sql rowid
            AllanCrump

            Sorry I assume people here understood it IS running on FM with Datadirect back-end for SQL queries via web.

            • 3. Re: sql rowid
              Sorbsbuster

              Sorry for being stupid.  It wasn't clear to me which was the database and which was the querying client.  I thought you had 10,000 words in a SQL database and you were wanting to write an sql query for Filemaker to pull in one word from that DB at random.

              The FM records will all have a unique Record ID (allocated by the file) or another unique ID allocated by you.  If you can generate a random number in that range (which I'm assuming from your post that you can) then you could simply select Where RecordID=TheRandomNumber.

              • 4. Re: sql rowid
                AllanCrump

                Ok, it seems that "RecordID" is not a Filemaker variable, however after hours of testing, I got it to work partially with the variable "rowid" which seems to be required in the beginning of the SELECT statement as follows:

                    $db->Sql("SELECT rowid,* FROM ICST WHERE NVA='N' AND rowid=$rndn ");

                However, the rowid's DO NOT start with 1, they start with 59742, and each row number does not correspond to records in the file as many numbers are unused. (For example records 59742, 59744, 59745, 59750, etc. so they are NOT sequential.)

                Can anyone explain this?

                • 5. Re: sql rowid
                  Sorbsbuster

                  You can create a calculation in the FM database = Get (RecordID).  You can create your own unique ID and serialise it from wherever to wherver in whatever steps you want.  So you can allocate the serial numbered personalised ID field to start at 1, in increments of 1.

                  • 6. Re: sql rowid
                    AllanCrump

                    Hmm.. looks like my version of Filemaker (9.0) does not yet support the Get(RecordID) function yet, although I doubt this would still work for me. Imagine you have 10,000 words and about 3,000 are Nouns and you only want to find the Nouns and pick a random record. Renumbering them or using individual serial numbers wouldn't do I'm afraid.

                    Thanks for the suggestions anyway.

                    • 7. Re: sql rowid
                      philmodjunk

                      Renumbering them or using individual serial numbers wouldn't do I'm afraid.

                      This is actually easy to do and need only be done once to update existing records. You can set this up so that any new records you add will get the next serial number value automatically.

                      • 8. Re: sql rowid
                        Sorbsbuster

                        I checked with a version I use, and FM 9 does support the Get ( RecordID).

                        • 9. Re: sql rowid
                          AllanCrump

                          Ok, let me be a little more specific as to why either of the suggestions would not work. Let's say we have a table of English words as follows: (The recordID field would be the invisible FM's internal record # as I discussed):

                          RecordID      Word   Type  Meaning

                          5807             Dog    Noun   ....

                          5810             Cat     Noun   ...

                          5811             Eat     Verb    ...

                          5815             Bird    Noun   ...

                          And let's say we have 10,000 words.. and we only want to get a count of the Nouns and pull a random one out to show on a webpage with its meaning using SQL:

                          ### Set random number;

                          $rndn = (any random number), i.e. 3

                          $rndn=$rndn + 5806        ## (to compensate for the internal records beginning with 5807)

                          $db->Sql("SELECT rowid,* FROM ICST WHERE NVA='N' AND rowid=$rndn ");

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

                          This call should return a list of records (5807, 5810,5815...) and open the third one (random #3) = value #5809 = does not exist

                          This is because FM doesn't use sequential numbers in its internal structure. If I used my own serial number in a field such as Get(RecordID) the result would be the same, and if I used my own serial number from a given field it would also return a list of unsequential numbers as I am choosing only 'N' (or nouns) from the list. See my problem?

                          I have the script partially working, however I am forced to repeat doing this until I get a valid record number by doing a GOTO loop which sometimes fails after 100 attempts, slowing the server and reducing performance.

                          • 10. Re: sql rowid
                            Sorbsbuster

                            If I can assume that NVA is for Noun_Verb_Adjective, then you could easily serialise the 3 catagories independently, then use your existing technique to search for a Noun, with that random number.

                            You could set the file up once by creating 3 fields SerialNoun, SerialVerb, SerialAdjective, and run a script once to populate the numbers.

                            • 11. Re: sql rowid
                              AllanCrump

                              This is possible. Another issue is that there are many levels involved. For example I want to later include the possibility of the script ONLY finding words that are that student's level and lower (no newer words). It will be used as flash cards for them as they can see included photo. Not sure how I'd incorporate this, and frankly if I add new words (as I do everyday), the list will grow.

                              • 12. Re: sql rowid
                                philmodjunk

                                Another option is to use the random number to select the ith record instead of matching to a value in a field. If you have 1,000 records in your recordset, generate a random integer from 1 to 1000 and use it to select that record.

                                In a strictly FileMaker setting, I can do this with the go to record step and use a calculation with the random function to compute the record number.

                                Don't recall how you'd do that in a SQL expression though it seems like that should be possible...

                                • 13. Re: sql rowid
                                  AllanCrump

                                  Yes, so back to the original question... How do I select the nth record of a found set using Filemaker's INTERNAL row (record) number? hehe

                                  • 14. Re: sql rowid
                                    Sorbsbuster

                                    You could try something like this:

                                    SELECT * FROM (
                                       SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101 )
                                    WHERE  RN = 100;

                                    Going to the row after the one you want and then coming back is apparently more efficient than using the MINUS operation.
                                    Obviously subsititute your table and field names.
                                    1 2 Previous Next