SQL Server clause on clause without using top, etc.
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 records. I was told to use a filter to check the latest entries for an IP address.
The problem is, as above, it will return the top 10 records and remove all those that don't match the filter, which means that in some cases it won't return anything.
I want it to return the 10 most recent entries of the given IP address (Filter key).
I tried to remove the "top 10", but it will not accept the order by clause, which means it will not necessarily give the latest entries.
As said, I need to use the filter key to match the rest of the project structure
I would recommend that you don't bake problems like string constraints, ordering and blocking hints in the view, as this will limit the usefulness / reusability of the view for different consumers. Instead, leave it to the caller to decide on such problems that might be applied retrospectively when using the view.
If you remove the string constraint from the view, then the filtering and string constraint can be executed from the caller:
SELECT TOP 10 *
FROM [dbo].[VW_ScanData]
WHERE FilterKey = 'FOO'
ORDER BY ID DESC;
However, the view then doesn't really add any value beyond direct selection from the table, other than antialiasing 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 user-defined function . The latter will allow you to bake all the problems you are interested in, and the filter key can be passed as a function parameter:
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 this ('Foo' is your filter):
SELECT *
FROM [dbo].[FN_ScanData]('FOO');
source to share