none
incorrect? results when using "top 1" in a view definition

    Question

  • I have a view that is to return the most recent measurement for an object from a table keyed on object_id.  I use a sub-select to identify the max(time) for the object_id, the outer select returns the single row per object based on max time for that object.  

    Actually the view joins the measurement table to a lookup table (object_id,ref_id) because the calling app only knows the ref_id.

    The table is very big with many objects, and the view is intended to be used to fetch the last measurement for only one object at a time.  We do not want to allow an unqualified query to fetch the last measurement for every object, so I put a "top 1" in the view definition.

    What I find is that the view works ok without the "TOP 1", returning the most recent measurement for any single object if I qualify on a valid REF_ID (and returning the last measurement for every object if no ref_id is specified, which is what we want to prevent.)  But if I add "TOP 1" to the outer query in the view definition, then the view will return no rows if the requested object is not the most recent one measured.  It seems that instead of finding the first qualifing row and then using "TOP 1" to prohibit fetching all objects, the "TOP 1" is applied too soon, and since the specified object was not the "TOP 1" no row is returned.  Is this correct behavior? 


    Friday, July 19, 2013 7:47 PM

Answers

  • If I understand you correctly, you have something like

    Create dbo.MyView As Select Top 1 ObjectID, <other data> From MyTable Order By MyDateTime Desc

    and when you do Select ObjectID, <other data> From dbo.MyView, you of course only get one row, for example, ObjectID = 1234.

    Then you do Select ObjectID, <other data> From dbo.MyView Where ObjectID = 7654 and that returns no rows.  If so, then yes, SQL Server is acting as it is supposed to do.  Your select statement efffectively says get all the rows that MyView will return (in this case one row with ObjectID = 1234) and then from that result return only the rows where ObjectID = 7654 and, of course, that is no rows.

    A fix would be to make this view into an inline table valued function.  An inline table valued function is in essence a view with parameters.  So you could do

    CREATE FUNCTION dbo.MyFunction(@ObjectID int)
    RETURNS TABLE
    AS
      RETURN (Select Top 1 ObjectID, <other data>
      FROM MyTable
      Where ObjectID = @ObjectID
      Order By MyDateTime Desc)
    

    Now when you call the function by something like Select <whatever> From dbo.MyFunction(7654) you will get the top row for ObjectID 7654.  And you don't have to worry about someone calling the function without specifying an ObjectID parameter.  If they do, they will get an error.

    Tom

    Friday, July 19, 2013 8:18 PM

All replies

  • If I understand you correctly, you have something like

    Create dbo.MyView As Select Top 1 ObjectID, <other data> From MyTable Order By MyDateTime Desc

    and when you do Select ObjectID, <other data> From dbo.MyView, you of course only get one row, for example, ObjectID = 1234.

    Then you do Select ObjectID, <other data> From dbo.MyView Where ObjectID = 7654 and that returns no rows.  If so, then yes, SQL Server is acting as it is supposed to do.  Your select statement efffectively says get all the rows that MyView will return (in this case one row with ObjectID = 1234) and then from that result return only the rows where ObjectID = 7654 and, of course, that is no rows.

    A fix would be to make this view into an inline table valued function.  An inline table valued function is in essence a view with parameters.  So you could do

    CREATE FUNCTION dbo.MyFunction(@ObjectID int)
    RETURNS TABLE
    AS
      RETURN (Select Top 1 ObjectID, <other data>
      FROM MyTable
      Where ObjectID = @ObjectID
      Order By MyDateTime Desc)
    

    Now when you call the function by something like Select <whatever> From dbo.MyFunction(7654) you will get the top row for ObjectID 7654.  And you don't have to worry about someone calling the function without specifying an ObjectID parameter.  If they do, they will get an error.

    Tom

    Friday, July 19, 2013 8:18 PM
  • It is hard to figure out your question without seeing your query or at least a query that resembles your actual query and the view definition. Can you provide them for better help?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, July 19, 2013 9:41 PM