TOP and LIMIT not working in sql




  • Avatar
    Sam Hughes

    When I try to use Limit, it only shows an error as the table preview. Any other ideas? That ranking workaround seems like a lot of steps for what is really simple in SSMS.


    Comment actions Permalink
  • Avatar
    Amministratore BI

    Thanks a lot,

    I'll try it


    Comment actions Permalink
  • Avatar
    Brian Roden

    I am having the same error when trying to use the standard TOP xx from SQL. I tried following the LIMIT example Sam provided, and it parses OK, but when I click Preview Result Table, I get and "Unexpected LIMIT" error from the server.

    Now my query has an ORDER BY Sales DESC, because I'm trying to get the top 20 selling product categories. I don't know if that is interfering with the LIMIT. But it parses OK, so the syntax must be correct.

    Comment actions Permalink
  • Avatar
    Mike Watkinson

    I am getting the same thing as Brian

    Comment actions Permalink
  • Avatar
    April Gunn

    Would love to know if anyone has found a solution to this as I'm having the same issue. In my case, I want to pull the earliest instance of a particular status from a table of work revision history. I'm creating a custom sql expression to pull this information with the following syntax:

    SELECT rh.ParentMarker,rh.Marker,rh.[Event Date-Time],rh.Status,rh.Operator FROM [database.revisionhistory] rh
    WHERE rh.Capsule='Work' AND rh.Status='COMPLETED' AND rh.ParentMarker LIKE '%FAC' ORDER BY rh.ParentMarker, rh.Marker LIMIT 1

    Which SHOULD be returning the first instance of a completed status for each work parentmarker in the database I'm querying. But instead it gives me a lot of angry yelling about unexpected limits. I've tried TOP as well and it returns a similar bit of yelling.

    Comment actions Permalink
  • Avatar
    Michael Becker

    Hi April,

    Why not use MIN aggregate on top of time column? So something like that:

    SELECT ParentMarker, Marker, Status, Operator, MIN(Event-Date-Time) FROM revisionhistory

    WHERE Capsule ='Wotk' and status='completed' and parentmarker like '%FAC'

    GROUP BY ParentMarker,marker,Status,Operator


    That should give you earliest entry for this particualr set of columns. Also you could try to use RANK, to rank entries based on time order, and then select on top of it only the entries with RANK = 1.

    Hope it helps.



    Comment actions Permalink
  • Avatar
    Roopa Raghavan

    Any updates on TOP/LIMIT possibility in custom SQL in Sisense?

    I found a way to just display the no. or records based on rank.

    So, I created a custom column called rownumber in the table using rank() and that ranked all of the records in the table.

    In my custom sql I can now  display first 9 items - 

    select *

    from [Table]

    group by col1, col2, col3

    having rownumber < 10

    I can also select any group of records based on rownumber.

    If my table were already sorted, I could get top 10 or bottom 10 records using this method.

    But I'm not able to order by and then limit no. of records yet.

    Any workarounds?

    Comment actions Permalink
  • Avatar
    Sam Ovenshine

    Hi Giovanni,

    You should be able to implement this for basic applications with the following syntax:

    SELECT *

    FROM Table

    LIMIT 100

    You may get an error when parsing the statement, but it should build correctly.

    Comment actions Permalink

Please sign in to leave a comment.