+ Reply to Thread
Results 1 to 6 of 6

Thread: TOP 100

  1. #1
    Andrew Timmins
    Join Date
    Apr 2010
    Posts
    250
    Points
    37
    Answers Provided
    6


    0

    Default TOP 100

    Is there any SQL that is valid with the FGDB that will help me limit the results returned?
    I have tried TOP, LIMIT etc.. but none work.

    I could do it all in code, but I would rather retrieve only the records i need from the table.

    I am using the .NET wrapper.

    Thoughts?

    Drew

  2. #2
    Anthony Giles

    Join Date
    Aug 2011
    Posts
    1,194
    Points
    1267
    Answers Provided
    201


    0

    Default Re: TOP 100

    Andrew,

    The help states the following:

    SELECT * FROM forms the first part of the SQL expression and is automatically supplied for you. Because you are selecting columns as a whole, you cannot restrict the SELECT to return only some of the columns in the corresponding table; thus, the hard-coded SELECT * syntax. For this reason, keywords like DISTINCT, ORDER BY, GROUP BY, and so on, cannot be used readily in a SQL query except when using subqueries.

    So what you could try and do (I must warn I have not tried this) is create a sub query that performs the limit then use the IN command to select out the records that you need, I.e:

    objectID IN (select objectID from (select objectID, column from 'table' where 'expression' order by 'column' limit 100))

    I say don't know if this will work but you never know

    Regards

    Anthony
    Last edited by ad_giles@hotmail.com; 10-14-2012 at 09:29 AM.

  3. #3
    Andrew Timmins
    Join Date
    Apr 2010
    Posts
    250
    Points
    37
    Answers Provided
    6


    0

    Default Re: TOP 100

    Thanks for the reply Anthony.

    I am able to get a full SQL query against the table using the below code..

    C#
    Code:
    string sql = "SELECT * FROM " + tableName
    RowCollection rows = geodatabase.ExecuteSQL(sql);
    From my knowledge the ExecuteSQL function will take raw valid SQL outlined in the doc's but the doc's do not show any way to limit the returned result (i.e LIMIT or TOP).

    Drew
    Last edited by cgishack; 10-14-2012 at 03:33 PM.

  4. #4
    Lance Shipman

    Join Date
    Oct 2009
    Posts
    390
    Points
    113
    Answers Provided
    19


    0

    Default Re: TOP 100

    TOP, LIMIT is not currently supported.
    Lance Shipman
    Geodatabase Product Engineer, File Geodatabase, SQLite
    Esri

  5. #5
    Tom Hoober
    Join Date
    Apr 2010
    Posts
    42
    Points
    0
    Answers Provided
    0


    0

    Default Re: TOP 100

    Lance are there any plans to support TOP functionality or any of the SELECT modifiers (ie DISTINCT)? I think its odd that there isn't a way to access full SQL query abilities. Thanks
    Tom Hoober

  6. #6
    David Sousa
    Join Date
    Jan 2011
    Posts
    53
    Points
    11
    Answers Provided
    1


    0

    Default Re: TOP 100

    I believe you will find that SELECT DISTINCT has worked all along.

    There are no immediate plans to implement SELECT TOP. I am not convinced that it is particularly useful. If you want the equivalent of SELECT TOP 100 * from xxx, why not just keep track of how many rows you have read in and then stop after you hit 100?

+ Reply to Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts