SQL Server order by clause without using top etc

Question

I have the following SQL view:

CREATE VIEW [dbo].[VW_ScanData]
AS
SELECT 
    top 10 ID,
    Chip_ID,
    [IPAddress] As FilterKey,
    [DateTime]
FROM         
TBL_ScanData WITH(NOLOCK)

ORDER BY ID DESC

GO

The idea is that this returns the 10 most recent entries. I have been told to to use a filterkey to check recent entries per IP Address.

The problem is that as it stands above, it will return the top 10 entries and remove all the ones that dont match the filter key which means in some cases it will not return anything.

I want it to return the 10 most recent entries of the given IP Address (Filter key).

I have tried removing 'top 10', but it will then not accept the order by clause, meaning it will not necessarily give the most recent entries.

As said, I need to use a filter key to comply with the rest of the framework of the project

score 2 · Answer 1 · edited May 23 '17 at 11:49

I would recommend that you do not bake concerns like row limits, ordering, and lock hints into a view, as this will limit the usefulness / reusability of the view to different consumers. Instead, leave it up to the caller to decide on such concerns, which can be applied retrospectively when using the view.

If you remove the row limit from the view, filter and row limit can then be done from the caller:

SELECT TOP 10 * 
FROM [dbo].[VW_ScanData]
WHERE FilterKey = 'FOO'
ORDER BY ID DESC;

That said, the view then doesn't really add any value beyond selecting from the table directly, other than the aliasing of IPAddress:

CREATE VIEW [dbo].[VW_ScanData]
AS
SELECT 
    ID,
    Chip_ID,
    [IPAddress] As FilterKey,
    [DateTime]
FROM
TBL_ScanData
GO

Edit
Other options available to you are using a stored procedure or a Table user defined function. The latter will allow you to bake in all the concerns you require, and the Filter key can be passed as a parameter to the function:

CREATE FUNCTION [dbo].[FN_ScanData](@FilterKey VARCHAR(50))
RETURNS @Result TABLE 
(
    ID INT,
    Chip_ID INT,
    FilterKey VARCHAR(50),
    [DateTime] DATETIME
)
AS
BEGIN
    INSERT INTO @Result
    SELECT 
        top 10 ID,
        Chip_ID,
        [IPAddress] As FilterKey,
        [DateTime]
    FROM         
        TBL_ScanData WITH(NOLOCK) -- This will bite you!
    WHERE
        [IPAddress] = @FilterKey
    ORDER BY ID DESC
    RETURN
END

Which you can then call like so ('Foo' is your filter key):

SELECT * 
FROM [dbo].[FN_ScanData]('FOO');

I agree with what you are saying, but in this case there is an old framework setup and I am limited as to what I can pass in to this view. It might be that I will have to look at altering the framework to achieve my aim — , Dec 23 '14 at 10:32
Can you use a user defined function in your framework? I've updated with an example. — StuartLC, Dec 23 '14 at 10:50
I cant use that either, I am going to try and alter the framework — , Dec 23 '14 at 10:52

score 0 · Answer 2 · answered Dec 24 '14 at 01:05

This select gets the last 10 entries per FilterKey.

select id,chip_id,FilterKey,[DateTime]
FROM (SELECT ID,
      Chip_ID,
      FilterKey,
      [DateTime],
      ROW_NUMBER() OVER (Partition By FilterKey Order BY ID DESC) AS RN
FROM TBL_ScanData WITH(NOLOCK) )
WHERE RN <= 10

SQL Server order by clause without using top etc

2 Answers2